vba 隐藏最后一行时查找 Excel 电子表格的最后一行

Finding the last row of an Excel spreadsheet when the last row is hidden

提问by Slothario

I'm trying to find the last row in column A that contains a value with the following code:

我试图在 A 列中找到包含具有以下代码的值的最后一行：

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

This works fine for most cases, exceptwhen 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.

这适用于大多数情况，除非最后几行被过滤掉。例如，假设我们有 30 行数据。如果1-10行可见，11-20行被过滤掉，21-30行可见，则成功找到最后一行：返回30。当一切都可见并且21-30行被过滤掉时，LastRow返回1。

Note that if I manually hideinstead of filtering out rows 21-30, it tells me that the last row is 20.

请注意，如果我手动隐藏而不是过滤掉第 21-30 行，它会告诉我最后一行是 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 unfilteredrow, 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.

编辑：现在似乎 LastRow 正在挑选最后一个未过滤的行，这与其以前的行为明显不同。一旦我能够更好地隔离我遇到的错误/不一致，我将更新这篇文章。

回答by MattCrum

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?

两者都不会在 A 列中找到最后使用的单元格，但是……这是您需要的吗？

回答by Jon Crowell

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).

这适用于具有隐藏行和自动过滤器的工作表。如果带有值的最后一个单元格下方的单元格已被格式化（这将导致 usedrange 大于您要查找的行），它也不会为您提供不正确的行。

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

回答by Teslarock rocktesla

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 :

在经历了很多挫折之后，看起来“vba 内置”方法总是存在问题。例如，列“A”和“WS”是一个工作表对象：

• ? 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

• ? Ws.Cells(WS.Rows.Count,1).End(xlUp) ? 隐藏行失败
• ? WS.Range("A1").Find(...) ? 当行隐藏在组中时失败（可能还有其他情况）
• ? 使用范围 ? 和 ？.SpecialCells(xlLastCell) ? 可以返回高于预期的结果

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

我的解决方案是使用带有“WorkSheet.Evaluate”的excel公式。

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

回答by Artur

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

回答by Tabias

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

我一直使用它来获取最后一行，但不能 100% 确定它适用于隐藏的单元格:)

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

PS 测试的时候一定要改好表名

'VBA to find last row
lngLastRow = ThisWorkbook.Worksheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

回答by Scott Holtzman

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.

这个怎么样（作为一种解决 XL 限制的方法）。循环有点长/笨重，但至少循环从第一个可见的最后一个单元格开始。

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