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

Thanks.

John


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

John,

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, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

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.

-Ben

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!