Dynamic range in excel macro without vba

Question

I want to select to the bottom of a list which changes in length but when I use ctrl shift end it records the absolute range, i thought i could use relative but can't get that to work. I'm not comfortable with vba, is there an easy way to do this during recording?

Answer 1

Since you don't want VBA, what about create a Name Range? Type the below formula in "Refers to" to get the dynamic range in column A

=OFFSET(Sheet1!$A$1,0,0,SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A))),1)

Answer 2

This will give you the last filled row in Col A on Sheet1

Sub getLastRow()

Dim lastRow As Long

With Worksheets("Sheet1")

    lastRow = .Cells(Rows.Count, "A").End(xlUp).Row

End With

Debug.Print lastRow

End Sub

Then you can use

Worksheets("Sheet1").cells(lastRow,"A").Select

to select the cell

Change sheetname to your sheet and "A" to the column you want