Clever Geek Handbook

Create a named range of all cells containing constants or formulas?

I am trying to create a named range that refers to all cells that contain formulas or constants. But I am getting an error on the line that starts with Set r = Union (...

How can I get this to work?

Dim r As Range

Set r = Union(Sheet1.Cells.SpecialCells(xlCellTypeConstants), Sheet1.Cells.SpecialCells(xlCellTypeFormulas), _
        Sheet22.Cells.SpecialCells(xlCellTypeConstants), Sheet22.Cells.SpecialCells(xlCellTypeFormulas))
+3


source to share


1 answer


Union

works only with ranges located on the same sheets. You can create a collection of addresses like this though

Sub Main()

    Dim arr As Variant

    arr = Array( _
                GetAddresses(Sheet1, xlCellTypeConstants), _
                GetAddresses(Sheet1, xlCellTypeFormulas), _
                GetAddresses(Sheet2, xlCellTypeConstants), _
                GetAddresses(Sheet2, xlCellTypeFormulas) _
                )

    Dim r As Variant
    For Each r In arr
        If Len(r) > 0 Then Debug.Print r
    Next

End Sub

Function GetAddresses(sh As Worksheet, cellType As XlCellType) As String
    On Error Resume Next
    GetAddresses = sh.Name & "!" & sh.Cells.SpecialCells(cellType).Address
    On Error GoTo 0
End Function


If you need to handle your errors differently, see this answer

+4


source






More articles:


All Articles