Finding the last row of an Excel spreadsheet when the last row is hidden
I'm trying to find the last row in column A that contains a value with the following code:
LastRow = DataWorksheet.Range("A:A").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
This works fine for most cases, except when the last few rows are filtered out. For instance, let's say we have 30 rows of data. If rows 1-10 are visible, 11-20 are filtered out, and 21-30 are visible, it finds the last row successfully: it returns 30. When everything is visible and rows 21-30 are filtered out, LastRow returns 1.
Note that if I manually hide instead of filtering out rows 21-30, it tells me that the last row is 20.
What gives? How can I make it determine what the last row is if the last rows are filtered?
Edit: Now it seems as though LastRow is picking out the last unfiltered row, which is a definite departure from its previous behavior. I'll update this post once I'm better able to isolate the bug/inconsistency I'm encountering.
These should ignore filtering / visibility and give you the last used row number:
DataWorksheet.UsedRange.Rows.Count
-or-
DataWorksheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
Neither will find the last used cell in column A, however... is that what you need?
This works on sheets with both hidden rows and autofilters. It will also NOT give you the incorrect row if a cell below the last cell with a value has been formatted (which will cause the usedrange to be greater than the row you are looking for).
Sub FindLastRowWithValue()
Dim ws As Worksheet
Dim temp As Worksheet
Dim lastrow As Long
' copy the sheet that may have hidden rows
Set ws = Sheets("Sheet1")
ws.Copy Before:=Sheets(1)
Set temp = ActiveSheet
' turn off autofiltering if need be
If temp.AutoFilterMode Then temp.AutoFilterMode = False
' unhide all rows
temp.Columns("A:A").EntireRow.Hidden = False
' get the last row with a value now that all rows are unhidden
lastrow = temp.Range("A" & temp.Rows.Count).End(xlUp).Row
' delete the temporary sheet
Application.DisplayAlerts = False
temp.Delete
Application.DisplayAlerts = True
MsgBox lastrow
End Sub
After a lot of frustration, looks like there is always issues with "vba built-in" methods. For example, with column "A", and "WS" being a WorkSheet Object :
My solution was to use an excel formula with "WorkSheet.Evaluate".
To check for non-empty value (ie a formula with an empty result WILL NOT be considered) :
Function FindLastRow(R as Range) As Long
Const NotFoundResult = 1 ' If all cells have an empty value, this value is returned
FindLastRow = R.Worksheet.Evaluate("IFERROR(LARGE(ROW('" & R.Worksheet.Name & "'!" & R.Address & ")*--('" & R.Worksheet.Name & "'!" & R.Address & " <> """"),1)," & NotFoundResult & ")")
End Function
To check for the last cell with a formula OR a value (even if the result is empty) :
Function FindLastRow(R as Range) As Long
Const NotFoundResult = 1 ' If all cells are empty (no value, no formula), this value is returned
FindLastRow = R.Worksheet.Evaluate("IFERROR(LARGE(ROW('" & R.Worksheet.name & "'!" & R.Address & ")*--(NOT(ISBLANK('" & R.Worksheet.name & "'!" & R.Address & "))),1)," & NotFoundResult & ")")
End Function
上一篇: 使用议案上的树莓派摄像头流媒体问题