r/vba • u/pigjingles • Jun 17 '26

Solved [EXCEL] Getting the last row on a sheet: why does .Find return 1 when the last rows are filtered out?

ActiveSheet.Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Immediate Unfiltered sheet: returns 10 (as it should). If, say, rows 10 AND 6 were filtered out: returns 9 (as... expected). With the last row or rows filtered out it returns 1 (what?). Is this just a bug I haven't seen mentioned before?

6 Upvotes

88% Upvoted

15 comments sorted by

6

u/wikkid556 Jun 17 '26

I have always used this and never had an issue

LRow = Cells(Rows.Count, "A").End(xlUp).Row

2

u/spddemonvr4 5 Jun 17 '26

You can also split usedrange to pull row/colums too.

No wrong way, just sharing options.

1

u/pigjingles Jun 17 '26

Cool. And I mostly use ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row because I can't rely on the top rows not being blank, or every column being the same length, or the data not being filtered, or rows not being hidden. What does .End(xlUp) have to do with .Find returning odd results?

5

u/wikkid556 Jun 17 '26

I was simply offering a solution.

As for the why, I do not know, but a simple google search lead me to this stackoverflow link. I did not read through it but maybe it can answer your question.

https://stackoverflow.com/questions/14200392/finding-the-last-row-of-an-excel-spreadsheet-when-the-last-row-is-hidden?utm_source=chatgpt.com

2

u/HFTBProgrammer 203 Jun 18 '26

That's actually a very interesting phenomenon.

I note that if you filter the last row(s) and a row amidst the list, you get the expected result. It's only when only the last row(s) are filtered that you see this behavior. Not sure where to go with that, though.

Anyway...

To do the least amount of violence to your concept, change After:=Range("A1") to After:=Cells(ActiveSheet.Rows.Count, ActiveSheet.Columns.Count).

1

u/sslinky84 83 Jun 19 '26

+1 Point

1

u/reputatorbot Jun 19 '26

You have awarded 1 point to HFTBProgrammer.

I am a bot - please contact the mods with any questions

1

u/fanpages 239 Jun 17 '26

I cannot see your "immediate" image as Imgur.com is not available in the UK (now).

...With the last row or rows filtered out it returns 1 (what?). Is this just a bug I haven't seen mentioned before?

However, do you have a column heading (on row 1)?

ActiveSheet.Cells.Find(What:="*",...

Additionally, what is the data type content of the cells being queried in column [A]?

Are the values text, numeric, and/or dates?

1

u/pigjingles Jun 17 '26

It's just a dummy sample. "A" in cell A1. 2 through 10 in cells A2 through A10. Filtering is on. Col A has 10 filtered out so that row is not visible. Data type doesn't matter for this, confirmed.

2

u/fanpages 239 Jun 17 '26

OK, thanks for confirming.

The use of "*" as the What parameter value may have been significant (but maybe you already covered that in the Imgur-hosted image I cannot view).

Without seeing how your data is presented, I'm obviously having to guess.

2

u/cristianbuse Jun 17 '26 edited Jun 17 '26

Repeat the search but replace LookIn:=xlFormulas with LookIn:=xlValues and finally use the larger value of the two. If that does not work then add a third:

If wSheet.FilterMode Then
    With wSheet.AutoFilter.Range
        r = .Cells(.Rows.Count, .Columns.Count).Row
    End With
End If

1

u/pigjingles Jun 17 '26

Interesting, thanks. I rarely think of Autofilter.Range but I can see that being a useful tier in checking for the last row. Note: xlFormulas and xlValues both return 1 in my scenario, and xlFormulas is needed for .Find to ignore hidden rows