MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VB to Select Last Row to Row 2

Posted by JohnH on July 05, 2001 12:01 PM

I would like to know how to copy every row from row 2 to the last row (used).



Posted by Ben O. on July 05, 2001 12:30 PM


First, here is a function to find to the last cell. I got the code from Aaron Blood's Excel Logic Page. You'll need to reference this function, so paste it into a module:

Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function

The following code selects from row 2 to the row of the last cell. You'll notice that it refernces the LastCell function:

Sub SelectTwoToLast()
Range(Cells(2, 1), LastCell(Sheets("Sheet1"))).EntireRow.Select
End Sub

I tested it, and it works.


Posted by JohnH on July 05, 2001 12:56 PM

Great Job Ben - Thanks!

Thank you for such a quick and useful response. Works like a charm!