I have a worksheet with an autofiltered range that starts in cell B3. Column A contains some macro buttons but is effectively blank. The top two rows contain information about the data in the main range.
In VBA, I am using what I believe is a standard method for determining the last row in a worksheet (In this case I cannot rely on the .End method on a single column):
LastRow = Activesheet.Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
However, sometimes this returns a value of one, even when I have thousands of rows of data. It seems to only do this when there are filters set (but there are still visible rows with data in them), but even then it doesn't always happen and I can't see a pattern to it.
I know there are other solutions - I have changed to a UsedRange technique instead, but it is very frustrating that this particular one fails as it would otherwise be the most effective one in this situation.
Does anyone know why this would be happening?
Have you thought of using Greg's answer, but looped to find the highest row of all the columns? Something like:
LastRow = 1
With ActiveSheet
For i = 1 to .UsedRange.Columns.Count
If .Cells(.Rows.Count, i).End(xlUp).Row > LastRow Then
LastRow = .Cells(.Rows.Count, i).End(xlUp).Row
EndIf
Next i
End With
This solution would allow for blank values randomly populated in bottom rows. UsedRange is tricky as it will return the furthest outlying row/column that has ever been edited (even if it is currently blank). In my experience Range.End(xlUp) behaves as you would expect if you pressed Ctrl-Up while in a worksheet. This is a little more predictable.
If you are set on using .Find try looking into the After:=[A1] argument. I haven't explored the idiosyncrasies of this function, but that would be the place I'd start given this problem.