Go to the last Row using VBA

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.
 
R

Ron de Bruin

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
 
H

humejap

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
 
R

rob nobel

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
 
Top