Locate Cells that are Occupied

Question

Using Excel 365 in Win 10

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

This gives:

I expected to see B2:G4,A2,H5.

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!

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

user_4851590 · Answer

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

Locate Cells that are Occupied

1 Answers