Go to the last Row using VBA

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

Does anyone know of a line of code I can put into my macro
that will allow me to go down to the last line of data
without using a loop.

For example whether I have 90 rows of data or 150. My
code would make the active cell the last one.

Thanks in advance for any help available.
 
Hi Nick

Example for the Active sheet

Sub test()
Cells(LastRow(ActiveSheet), 1).Select

End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
 
As long as you have a column that has data for all rows you can use
End(xlDown) to get to the last row.

E.g. Assuming you data starts in Row 1 and that column A has an entry
for each line of data then use

Range("A1").End(xlDown).Select

If you want to get the last cell that has ever had data ( e.g. the
equivalent of doing Ctrl+End then use

Range("A1").SpecialCells(xlCellTypeLastCell).Select
 
One way Nick is...
Range("A65536").End(xlUp)(2, 1).Activate
Which will activate the next blank cell in Column A

If you want to activate the last cell with data in col A use...
Range("A65536").End(xlUp)(1, 1).Activate

Rob
 
Back
Top