Hi.
Both of these codes find the last row in column A but which is the most efficient way (computer resources use etc.)
Range("A65536").End(xlUp).Select
OR
finalrow = Cells(Rows.Count, 1).End(xlUp).Select
Thank You
Regards,
Peter
Hi.
Both of these codes find the last row in column A but which is the most efficient way (computer resources use etc.)
Range("A65536").End(xlUp).Select
OR
finalrow = Cells(Rows.Count, 1).End(xlUp).Select
Thank You
Regards,
Peter
Regards, Peter.
1. Old versions of Excel only have 65K rows so I don't hard code a 'magic number'
2. Your examples are wrong:
finalrow = Cells(Rows.Count, 1).End(xlUp).Select
I'm guessing you meant something like
Set finallrow = Cells(Rows.Count, 1).End(xlUp).EntireRow ... or ... finalrow = Cells(Rows.Count, 1).End(xlUp).Row ... or ... Cells(Rows.Count, 1).End(xlUp).EntireRow.Select
depending of whether
1. You want the last row that has data in A stored in a Range variable
2. You just want the row number of the last row that has data in A
3. You want to Select the last row that had data in A
If the last used cell in A is (e.g.) A100, and the last used cell in B is B200, these will act on row 100
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
i don't you will notice the performance differences.
all four methods below will give you the last row with data, assuming that you have a proper, well designed table (table's topleft cell is A1 and column 1 and row 1 contain no blank cells).
run it from the VBE window and make sure the Immediate Window is open.
if you applied autofilters and the last row before autofilter is filtered, the first three will give you last row after autofilter, ie last visible row with data, but the forth will give the last row's number as if no filters have been applied.Sub last_row() Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Row Debug.Print Cells(Rows.Count, 1).End(xlUp).Row Debug.Print Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Debug.Print Range("A1").CurrentRegion.Rows.Count End Sub
so it depends on your requirement.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Hi Paul,
Thank you very much, you have given me a good understanding of the differences and their uses.
Regards,
Peter
Regards, Peter.
Great information Mancubus, thank you. I will experiment with your and Paul's suggestions.
Regards,
Peter
Regards, Peter.