9

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.

Community
  • 1
  • 1
Slothario
  • 2,830
  • 3
  • 31
  • 47

7 Answers7

3

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?

MattCrum
  • 1,110
  • 1
  • 6
  • 5
  • Yep. I tried both of those, but all I'm really interested in is the last cell with a value in it. – Slothario Jan 07 '13 at 17:24
  • These answers don't give the last cell with a value, just the `UsedRange`. PS `UsedRange` does have to start in cell `A1` and is often times is the entire sheet if the sheet has large data sets that have been updated over time. – Profex Feb 13 '18 at 17:19
2

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
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • @slothario: did you try this? – Jon Crowell Jan 15 '13 at 15:57
  • Thanks for getting back to me! I'm currently rushing to meet a deadline at work so I currently have to put this issue aside and come back to it, but I'll make sure to update this thread when I'm able to get to the bottom of it. – Slothario Jan 17 '13 at 15:42
1

How about this (as a sort of work around on XL's limitations). It's kind of long / clunky with the loop, but at least the loop starts at the first visible last cell.

LastRow = DataWorksheet.Range("A:A").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row


If LastRow <> DataWorksheet.UsedRange.Rows.Count 'assumes data starts in A1, if not adjust acoordingly

'now check if there is anything below

    Dim rngSearch as Range
    rngSearch = DataWorksheet.Range("A" & LastRow & ":A" & DataWorksheet.UsedRange.Rows.Count)

    Dim lngRows as Long, lngCnt as Long
    lngRows = rngSearch.Rows.Count

    For lngCnt = lngRows to 1 Step -1

        If DataWorksheet.Range("A" & lngCnt) = vbNullString And DataWorksheet.Range("A" & lngCnt -1) <> vbNullString Then

            LastRow = DataWorksheet.Range("A" & lngCnt-1).Row
        End If

    Next

End If
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
1

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 :

  • « Ws.Cells(WS.Rows.Count,1).End(xlUp) » fails with hidden rows
  • « WS.Range("A1").Find(...) » fails when there is rows hidden in groups (and maybe other circumstances)
  • « UsedRange » and « .SpecialCells(xlLastCell) » can return a result higher than expected

My solution was to use an excel formula with "WorkSheet.Evaluate".

To check for non-empty value (i.e. 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
Teslarock rocktesla
  • 23
  • 5
  • Works very nice, just a bit slow if you don't limit the range. I suggest using something like `Sheet.UsedRange.Columns(Col)`, if you want a specific column. – Profex Feb 13 '18 at 17:14
1

I assume that Slothario's sheet has a simple structure, with the following key features:

  • The last populated cell in column A is also the last populated row of the sheet (at least when no rows are hidden or filtered out).
  • There is something in row 1

In that case, here are two simple ways to identify the different kinds of 'last row':

Cells.SpecialCells(xlLastCell).Row 'Last row that is not hidden or filtered out

Activesheet.UsedRange.Rows.Count 'Last row with a value in it (even if the row is hidden; only gives right answer if row 1 is nonblank)

If the top row/rows might be blank, the second option needs to be modified to this:

Activesheet.UsedRange.Rows.Count + Activesheet.UsedRange.Row -1 'Last row with a value in it (even if the row is hidden)
LondonJustin
  • 73
  • 1
  • 1
  • 7
  • not sure how your answer contributes anything that wasn't already provided in MattCrum's answer - these are the same methods he described 8 years ago. Would you care to elaborate on why you felt another answer was needed instead of just leaving a comment or making an edit to the previous answer? – CBRF23 Jul 06 '21 at 21:20
  • I contributed information about how MattCrum's two methods differ, tailored to the use case in the original question. My rep is only 41 and I don't have permission to add comments on others' answers. As for editing the previous answer, perhaps I should have done that, I wasn't aware that it was possible. Thanks for the tip. – LondonJustin Jul 08 '21 at 11:45
  • No problem. I saw your answer in a review queue for new answers to old questions. Rather than flagging ir or down voting, I thought I would ask. Thanks for responding. – CBRF23 Jul 08 '21 at 12:26
0

I use this all the time to get the last row, not 100% sure it works for hidden cells though :)

P.S. make sure to change the sheet name when testing

'VBA to find last row
lngLastRow = ThisWorkbook.Worksheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Tabias
  • 100
  • 9
0
Public Function lr(ByVal source As Worksheet, colu As Long) As Long
Dim tmp As Long
tmp = source.Cells(source.Rows.Count, colu).End(xlUp).Row

While source.Cells(tmp + 1, colu).EntireRow.Hidden = True
tmp = tmp + 1
Wend
lr = tmp
End Function
Artur
  • 1