Finding Last row using usedrange

Question

I am creating a large macro code for automating my daily office report. I am using following code for getting Last row.

Lrow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row

in what cases this code will end up giving wrong results.

Like using "xlup from last cell of column" is useless when data is not in tabular form. Eg. column 1 have 10 cells & column 2 have 25 cells.

I've found the range.Find with xlByRows and xlPrevious to be the most reliable method for ragged data. — , Mar 25 '19 at 10:52
@CallumDA Tag description says *THIS TAG IS PENDING REMOVAL. Instead, please use tags [excel] and [vba]. (More info: https://meta.stackoverflow.com/q/370095)* — Pᴇʜ, Mar 25 '19 at 11:03
Gaurav if you feel this is not a duplicate or the linked post doesn't provide a solution to your query then let me know and I will re-open it :) — Siddharth Rout, Mar 25 '19 at 11:03
@SiddharthRout, while the question isn't a duplicate -- the answer definitely covers this question too (nice answer btw). If only that other question was called something more like *"How to find the last row on a sheet"* — CallumDA, Mar 25 '19 at 11:07
@SiddharthRout I am trying this code, which is not frequently used. I want you guys to let me know, in which situations my code can go wrong? — Gaurav Jain, Mar 25 '19 at 11:21
@SiddharthRout Yup, I saw your answer & also agree that it gives correct answer. But I am trying alternate options. — Gaurav Jain, Mar 25 '19 at 11:26
No I meant, Did you read about the "usedrange" part. I was referring to `I want you guys to let me know, in which situations my code can go wrong? – Gaurav Jain 7 mins ago` — Siddharth Rout, Mar 25 '19 at 11:28
It took some time. But I read that complete thread. Thank you. Got what I needed. Have a nice day everyone. — Gaurav Jain, Mar 25 '19 at 11:51

score 1 · Accepted Answer · answered Mar 25 '19 at 10:53

1

You can find the last Row in a sheet using find. e.g.

LastRow = ActiveSheet.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row

answered Mar 25 '19 at 10:53

Tom

I usually include something like an `After:=ActiveSheet.Cells(ActiveSheet.RowsCount, ActiveSheet.ColumnsCount)` argument. – Mar 25 '19 at 10:56
@user11246173 I don't think you need it - but doesn't hurt either – Tom Mar 25 '19 at 10:57
Can you try this code with a excel data in which column 1 have 10 cells & column 2 have 25 cells. It end up giving Row("10") as last row, while my last row is Row("25") – Gaurav Jain Mar 25 '19 at 11:00
@GauravJain I've set up a workbook with 10 rows in column 1, and 25 rows in column 2 and it comes back with 25. Can you update your question with the code that you're trying and is failing? – Tom Mar 25 '19 at 11:05
Done its working. But can you tell me issues in my code. – Gaurav Jain Mar 25 '19 at 11:19
Nothings wrong with your code. Just `UsedRange` can give unexpected results so is usually best to avoid when trying to get the last row of a sheet. Have a look at this answer - it explains it a bit more. [link](https://stackoverflow.com/a/50434593/3042759) – Tom Mar 25 '19 at 11:27

1 Answers1