找到被占用的单元格 [英] Locate Cells that are Occupied

问题描述

在Win 10中使用Excel 365

Using Excel 365 in Win 10

我在定位/识别正在使用的工作表单元格时遇到了一个非常小的问题.例如，在单元格 A1 和单元格 H5 中，存在常量.单元格 B2 包含可溢出的动态数组常量:

I am having a very small problem locating/identifying worksheet cells that are being used. For example in cell A1 and cell H5 there are constants. Cell B2 contains the spill-able dynamic array constant:

={1,2,"",4,5,6;7,8,9,"",11,99;100,"",0,0,100,0}

因为此工作表同时包含公式和常量，所以我尝试了可信赖的方法:

Because this sheet contains both formulas and constants, I tried my trusty:

Sub LocateCellsWithStuffInThem()
    Dim rng As Range

    With ActiveSheet.Cells
        Set rng = Union(.SpecialCells(xlCellTypeFormulas), .SpecialCells(xlCellTypeConstants))
    End With
    MsgBox rng.Address(0, 0)
End Sub

这给出了:

我希望看到 B2:G4，A2，H5 .

显然正在使用像 D2 这样的单元格.即使 SpecialCells 认为它不填充公式或常量且长度为零，它也是数组常量的一部分！

A cell like D2 is clearly being used. It is part of the array constant even though SpecialCells does not consider it filled with either a formula or a constant and has zero length!

如何将代码编写到易于定位的占用单元中?我是否必须遍历 UsedRange 中的所有单元格?

How can I write code to easily located occupied cells? Do I have to loop over all the cells in UsedRange?

推荐答案

您可以循环公式单元格并添加溢出区域(如果有的话):

You can loop the Formula cells and add the spill area if it has it:

Sub LocateCellsWithStuffInThem()
    Dim rng As Range
    Dim rng2 As Range
    With ActiveSheet.Cells
        Set rng = .SpecialCells(xlCellTypeConstants)
        For Each rng2 In .SpecialCells(xlCellTypeFormulas).Cells
            If rng2.HasSpill Then
                Set rng = Union(rng2.SpillingToRange, rng)
            Else
                Set rng = Union(rng2, rng)
            End If
        Next rng2
    End With
    MsgBox rng.Address(0, 0)
End Sub

这篇关于找到被占用的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！