Reference entire sheet in Excel formula

Ken Krugh 116 Reputation points

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.


A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,081 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,038 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 106.4K Reputation points

    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