Excel VBA - Found last row number but can't select it

[andibevan]

Hi there,

I am using the following code that I have been told is fairl
infallable at finding the last populated row of my data table. Th
routine Last_Row then displays the last row number. What I can'
manage to figure out how to do is actually select column A of the Las
row - sure it is simple but I am missing it.

Thanks

Andi

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

----------------------------------------------

Sub Show_Last_Row()
MsgBox LastCell(Sheet2).Cell

End Su

 

[andibevan]

Sorry second routine should be:-

Sub Show_Last_Row()
MsgBox LastCell(Sheet2).Row

End Su

 

[Charles]

andi,

not tested but I think this will give you the last row in Column "A"


Charles


sub last_row

Dim LastRow As Range
Set LastRow = Sheet2.Range("a65536").End(xlUp).Row
MsgBox LastRow(Sheet2).Row

End Su

 

[JWolf]

Sub test()
Cells(LastCell(Sheet2).Row, 1).Select
End Sub

 

[Bob Phillips]

Set last = LastCell(Sheet2)
Cells(last.Row,"A").Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[andibevan]

How would I then copy the contents of the cells from row A2:AA2 o
Sheet3 (Sheet 3) to the last row?

Any help would be gladly received.

Thanks

And

 

[Tom Ogilvy]

worksheets("Sheet3").Range("A2:AA2").Copy _
Destination:=worksheets("Sheet2").Cells(rows.count,1).End(xlup)(2)

 

[MSP77079]

All of this works as long as no one has made any extraneous entrie
below your data table. But, it's going to fail if the worksheet i
not absolutely, infallibly clean.

When I have a table of data, I don't use "search for *". Nor do I us
.end(xlUp). Instead, I use "CurrentRegion". It is not fooled by th
occassional blank cell, nor extraneous stuff at the end of th
datatable.

Let's say that cell "B2" is the first cell in the data table. Then,

Set myRange = Range("B2").CurrentRegion
LastRow = myRange.Cells(myRange.Cells.Count).Row
LastCol = myRange.Cells(myRange.Cells.Count).Column

worksheets("Sheet3").Range("A2:AA2").Copy _
Destination:=worksheets("Sheet2").Cells(LastRow, LastCol

 

[Tom Ogilvy]

Certainly applicable to appropriate situations, but it can be tripped up as
well by the occasional blank row or blank column.