Defining Named Range by Formula or UDF

Question

I would like to ask about your experience with defining a named range in Excel.

I'm normally using this formula:

=OFFSET(Sheet!$A$1;0;0;COUNTA(Sheet!$A:$A);COUNTA(DATA!$1:$1))

This works great for a dynamic range. It modifies once you add rows or columns to the original table. Problem appears once some data are input outside of the table in column A or row 1 in this case.

Then I thought Range("A1").CurrentRegion.Address in a UDF will do the job. But this command is skiped in a UDF... as it doesn't work only with the cell which contains the formula, I guess.

Anyone using some formula or UDF which is more robust?

Problem appears once some data are input outside of the table What problem? — Ron Rosenfeld
There are numerous posts detailing various methods of finding the 'last cell' with the benefits and caveats of each method. — user11217663
use another column or row to count by, Restrict entry of information below the Table in column A or addin information to the right in row 1 — Forward Ed

Tim Williams Tim Williams · Accepted Answer · 2019-03-19T21:44:26

Something like this could work, but would be dependent on having no "interfering" content on the sheet:

' "rng" is the top-left corner of the table you want to return
Function FullTable(rng As Range) As Range
    Dim lastCol As Long, lastRow As Long

    With rng.Parent
        lastRow = .Cells(.Rows.Count, rng.Column).End(xlUp).Row
        lastCol = .Cells(rng.Row, .Columns.Count).End(xlToLeft).Column
        Set FullTable = .Range(rng, .Cells(lastRow, lastCol))
    End With

End Function

Defining Named Range by Formula or UDF

1 Answers