(Excel) reliable way to find the last row with a value, supporting hidden and autofiltered not contiguous rows
I need a function that returns the last used (filled with a value) row in an Excel sheet, supporting ALL the situations.
Usedrange method is not good because includes formatted unused cells.
Find method is not good because it doesn't include autofiltered rows.
xlUp method is not good because it acts on a single specified column.
xlDown method is not good because it doesn't pass through intermediate unused cells.
xlCellTypeLastCell has some of the above limitations.
xlCellTypeVisible count doesn't support non-contiguous visible range of rows.
I included a little sample sheet where any of the above methods fails.
No method returns the correct value "5".
What's the solution?
test.xlsx
Usedrange method is not good because includes formatted unused cells.
Find method is not good because it doesn't include autofiltered rows.
xlUp method is not good because it acts on a single specified column.
xlDown method is not good because it doesn't pass through intermediate unused cells.
xlCellTypeLastCell has some of the above limitations.
xlCellTypeVisible count doesn't support non-contiguous visible range of rows.
I included a little sample sheet where any of the above methods fails.
No method returns the correct value "5".
What's the solution?
test.xlsx
ASKER
No, I tried CTRL+ END on my test sheet and it places the cursor on row 3 while the last filled cell is in row 5!
(I need to create a VBA function that reliably returns the last used row)
(I need to create a VBA function that reliably returns the last used row)
This works with both text and numbers and doesn't care if there are blank cells, i.e., it will return the last non-blank cell. It needs to be array-entered, meaning that you press Ctrl-Shft-Enter after you type or paste it in. The below is for column A:
=INDEX(A:A,MAX((A:A<>"")*(
=INDEX(A:A,MAX((A:A<>"")*(
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi,
In case it was not clear, taking LLCSAM's suggestion, & making this work for your example workbook:
=MAX((A:C<>"")*(ROW(A:C)))
This returns 5, as you requested.
However...
Is this the case? Do you need a solution in Visual Basic for Applications code?
BFN,
fp.
In case it was not clear, taking LLCSAM's suggestion, & making this work for your example workbook:
=MAX((A:C<>"")*(ROW(A:C)))
This returns 5, as you requested.
However...
(I need to create a VBA function that reliably returns the last used row)
Is this the case? Do you need a solution in Visual Basic for Applications code?
BFN,
fp.
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPreviou
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
That results in 3, not 5, Martin, as it does not take account of rows hidden when a Filter is applied.
Sorry about that, I just tested with hidden rows. Try this.
Dim lngFilteredRows As Long
Dim lngLastRow As Long
lngFilteredRows = AutoFilter.Range.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + lngFilteredRowsASKER
Martin. You wrote "hidden" but actually there's difference between hidden and autofiltered rows and it must support both.
Does your solution support both?
Does your solution support both?
ASKER
Martin, your last code returns runtime error 424
When I said
In the sheet you provided, my code in post ID: 39335029 runs fine and returns 5 but it doesn't work if I hide one of the visible rows. In any case this brute force method works with both hidden and filtered rows (at least with the sheet you provided).
Sorry about that, I just tested with hidden rows.what I meant was that in my previous testing I had tested with hidden rows rather than filtered rows, but in the code in post ID: 39335029 I tested with filtered rows.
In the sheet you provided, my code in post ID: 39335029 runs fine and returns 5 but it doesn't work if I hide one of the visible rows. In any case this brute force method works with both hidden and filtered rows (at least with the sheet you provided).
Dim lngCol As Long
Dim lngRow As Long
Dim lngLongest As Long
Dim lngLastUsedRow As Long
lngLastUsedRow = ActiveSheet.UsedRange.Rows.Count
For lngCol = 1 To 3
For lngRow = lngLastUsedRow To 1 Step -1
If Cells(lngRow, lngCol).Value <> "" Then
Exit For
End If
Next
If lngRow > lngLongest Then
lngLongest = lngRow
End If
Next
MsgBox "Last row with data (including both filtered and hidden rows) is " & lngLongestASKER
I would need it as a function for general use... in Excel 2010...
How much time would it take to search through 1 billion (1.000.000 rows x 1000 cols) cells? :)
How much time would it take to search through 1 billion (1.000.000 rows x 1000 cols) cells? :)
In your 1.000.000 rows I assume that most of the columns contain data almost to the last row, and since the code starts at the bottom and works it's way up, it shouldn't take too long. You can test it and find out:)
BTW, my code assumes that there are just the 3 columns that you showed in your sheet. To account for the 1000 columns change line 7 above to say 1000 rather than 3 or you can do this.
BTW, my code assumes that there are just the 3 columns that you showed in your sheet. To account for the 1000 columns change line 7 above to say 1000 rather than 3 or you can do this.
Dim lngCol As Long
Dim lngRow As Long
Dim lngLongest As Long
Dim lngLastUsedRow As Long
Dim lngLastCol As Long
lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
lngLastUsedRow = ActiveSheet.UsedRange.Rows.Count
For lngCol = 1 To lngLastCol
For lngRow = lngLastUsedRow To 1 Step -1
If Cells(lngRow, lngCol).Value <> "" Then
Exit For
End If
Next
If lngRow > lngLongest Then
lngLongest = lngRow
End If
Next
MsgBox "Last row with data (including both filtered and hidden rows) is " & lngLongestASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
My code doesn't loop through every row, only rows at the bottom with empty cells.
My code doesn't loop through every row, only rows at the bottom with empty cells.
:)
Yes, that is what I meant.
Did any of the above work for you?
ASKER
MartinLiss:
If I add two values in E9 and E10 and then I autofilter out E10 and hide the column E, it fails to find 10 as last line
If I add two values in E9 and E10 and then I autofilter out E10 and hide the column E, it fails to find 10 as last line
ASKER
fanpages your solution seems to always work!
I keep trying it...
I keep trying it...
fanpages your solution seems to always work!
If that is the case, thank you in advance for selecting the solution you are using & closing this question thread.
PS. For information, for those contributing to this question, here is another on the same subject:
"(Excel) speed-up a loop using dichotomic logic"
[ https://www.experts-exchange.com/questions/28203209/Excel-speed-up-a-loop-using-dichotomic-logic.html ]
"(Excel) speed-up a loop using dichotomic logic"
[ https://www.experts-exchange.com/questions/28203209/Excel-speed-up-a-loop-using-dichotomic-logic.html ]
Not sure if you needed code or not for this.
EDIT: Never mind I see what you are trying to do now. I will look into it further for you.