Create named range of all cells that contain constants or formulas?

Question

I am trying to create a named range that refers to all cells containing formulas or constants. But I get an error message on the row 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))

does `Union` work with ranges on multiple sheets? – Nov 06 '14 at 13:58 — , Nov 06 '14 at 13:58
That seems to be the problem. Thanks! – user1283776 Nov 06 '14 at 14:05 — user1283776, Nov 06 '14 at 14:05

score 4 · Accepted Answer · edited May 23 '17 at 10:29

Union only works with Ranges that are on the same sheets. You can build a collection of the 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, have a look at this answer

++ Nicely Done! Imp suggestion: When working with `SpecialCells`, It is imp that you handle the errors :) — Siddharth Rout, Nov 06 '14 at 14:26

Create named range of all cells that contain constants or formulas?

1 Answers1