Getting round the limitations of Excel VBA’s UsedRange function

Turns out that the Excel VBA function UsedRange doesn’t always give the expected results. It gives you the range of cells that have ever been used. If you’ve formatted them, or they used to have values in that you’ve since deleted, you still get those cells. Today I wanted to iterate all cells in a lot of sheets. Some of those sheets had lots of cells in their UsedRange. I hit upon a solution I’ve not seen anywhere else: take the union of constants and formulas in a sheet. This  is quick, because you can use SpecialCells to do the heavy lifting. And you can refine the results to exclude errors using SpecialCells Value argument (the default 23 below selects everything). Here’s the code

Function myUsedRange(ws As Worksheet, Optional lookfor As Long = 23) As Range
    Dim c As Range, f As Range
    On Error Resume Next
    With ws.Range("A1")
        Set c = .SpecialCells(xlCellTypeConstants, lookfor)
        Set f = .SpecialCells(xlCellTypeFormulas, lookfor)
        If c Is Nothing Then Set c = .Cells
        If f Is Nothing Then Set f = .Cells
    End With
    Set myUsedRange = Union(c, f)
End Function

This returns a non-contiguous range containing only the cells you want. This version is a bit lazy and includes [A1] if there are no constants or no formulas matching your search. I didn’t mind because I was using lookfor=2 to search for text which reduced the number of cells by thousands. Hundreds of thousands in some cases. A better version would take a default as a parameter and return it only if neither  constants or formulas gave any results.