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

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

--
Message posted from http://www.ExcelForum.com

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

Sorry second routine should be:-

Sub Show_Last_Row()
MsgBox LastCell(Sheet2).Row

End Su

--
Message posted from http://www.ExcelForum.com

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

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

--
Message posted from http://www.ExcelForum.com

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

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

andibevan < wrote:
Hi there,

I am using the following code that I have been told is fairly
infallable at finding the last populated row of my data table. The
routine Last_Row then displays the last row number. What I can't
manage to figure out how to do is actually select column A of the Last
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 Sub


---
Message posted from http://www.ExcelForum.com/

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

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 " wrote in message
...
Hi there,

I am using the following code that I have been told is fairly
infallable at finding the last populated row of my data table. The
routine Last_Row then displays the last row number. What I can't
manage to figure out how to do is actually select column A of the Last
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 Sub


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

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

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

--
Regards,
Tom Ogilvy

"andibevan " wrote in message
...
How would I then copy the contents of the cells from row A2:AA2 on
Sheet3 (Sheet 3) to the last row?

Any help would be gladly received.

Thanks

Andi


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

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

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

--
Regards,
Tom Ogilvy

"MSP77079 " wrote in message
...
All of this works as long as no one has made any extraneous entries
below your data table. But, it's going to fail if the worksheet is
not absolutely, infallibly clean.

When I have a table of data, I don't use "search for *". Nor do I use
end(xlUp). Instead, I use "CurrentRegion". It is not fooled by the
occassional blank cell, nor extraneous stuff at the end of the
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)


---
Message posted from http://www.ExcelForum.com/