Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract number from cell when specific format is found | Excel Worksheet Functions | |||
Count the number of Commas found in cell A2 | Excel Worksheet Functions | |||
How to select the number in excel? | Excel Worksheet Functions | |||
Advanced Filter: Number of Records Found | Excel Discussion (Misc queries) | |||
is there someway to select what i have "found"? | New Users to Excel |