Reference entire sheet in Excel formula

Question

Is there something I can put in a formula that is equivalent to .UsedRange in VBA?

I'd like to pass the entire used range as the parameter to a custom function without having to update the parameter each time the number of rows in the sheet grows or shrinks.

Thanks,
Ken

Answer 1

I think that ActiveSheet.UsedRange will give you the range. Therefore you can use a formula like =MyFunction() without parameter.

The function is, for example:

Public Function MyFunction() As String

    Application.Volatile True

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim r As Range
    Set r = ws.UsedRange
    
    '. . . perform calculations and return the result . . .

    MyFunction = "the result"

End Function