在 Excel VBA 中查找上次使用的单元格

Question

当我想查找最后使用的单元格值时，我使用：

Dim LastRow As Long

LastRow = Range("E4:E48").End(xlDown).Row

Debug.Print LastRow

当我将单个元素放入单元格时，我得到错误的输出。但是，当我将多个值放入单元格时，输出是正确的。 这背后的原因是什么？

Answer 1

注意：我打算将其设为“一站式帖子”，您可以使用正确方式找到最后一行。这还将涵盖查找最后一行时要遵循的最佳实践。因此，每当我遇到新的场景/信息时，我都会继续更新它。

---

查找最后一行的不可靠方法

查找最后一行的一些最常见方法非常不可靠，因此永远不应该使用。

1. UsedRange
2. xlDown
3. CountA

UsedRange 应该决不用于查找最后一个有数据的单元格。这是非常不可靠的。尝试这个实验。

在单元格 A5 中输入一些内容。现在，当您使用下面给出的任何方法计算最后一行时，它会给您 5。现在将单元格 A10 涂成红色。如果您现在使用以下任何代码，您仍然会得到 5。如果您使用 Usedrange.Rows.Count 您会得到什么？它不会是 5。

这是一个展示 UsedRange 如何工作的场景。

xlDown 同样不可靠。

考虑这段代码

lastrow = Range("A1").End(xlDown).Row

如果只有一个单元格 (A1) 有数据，会发生什么情况？您最终将到达工作表中的最后一行！这就像选择单元格A1，然后按End键，然后按向下箭头键。如果范围内存在空白单元格，这也会给您带来不可靠的结果。

CountA 也不可靠，因为如果中间有空白单元格，它会给出错误的结果。

因此，应该避免使用 UsedRange、xlDown 和 CountA 来查找最后一个单元格。

---

查找列中的最后一行

要查找 Col E 中的最后一行，请使用此命令。

With Sheets("Sheet1")
    LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With

如果您注意到 Rows.Count 之前有一个 .。我们常常选择忽视这一点。有关您可能遇到的错误，请参阅此问题。我总是建议在 Rows.Count 和 Columns.Count 之前使用 .。该问题是一个典型的场景，其中代码将失败，因为 Rows.Count 对于 Excel 2003 及更早版本返回 65536，对于 Excel 2007 和更早版本返回 1048576之后。同样，Columns.Count 分别返回 256 和 16384。

上述 Excel 2007+ 有 1048576 行的事实也强调了这样一个事实：我们应该始终将保存行值的变量声明为 Long 而不是 Integer 否则您将收到Overflow 错误。

请注意，此方法将跳过任何隐藏行。回顾一下 我上面 A 列的屏幕截图，如果第 8 行被隐藏，此方法将返回 <代码>5而不是8。

---

查找工作表中的最后一行

要查找工作表中的Effective 最后一行，请使用此功能。请注意 Application.WorksheetFunction.CountA(.Cells) 的使用。这是必需的，因为如果工作表中没有包含数据的单元格，则 .Find 将为您提供运行时错误 91：未设置对象变量或 With 块变量

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With

---

Find Last Row在表 (ListObject) 中也

适用相同的原则，例如获取表第三列中的最后一行：

Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1")  'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")

With tbl.ListColumns(3).Range
    lastrow = .Find(What:="*", _
                After:=.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
End With

End Sub

Answer 2

注意：这个答案的动机是

至于找到最后使用的单元格的正确方法，首先要决定考虑什么< em>使用过，然后选择合适的方法。我至少想到了三个含义：

1. Used=非空白，即有数据。

2. 已使用 =“...正在使用，表示包含数据或格式的部分。”
   根据官方文档，这是Excel在保存时使用的标准。另请参阅此官方文档。
   如果没有意识到这一点，该标准可能会产生意想不到的结果，但它也可能被有意利用（当然，不太频繁），例如，突出显示或打印特定区域，这些区域最终可能没有数据。
   当然，最好将其作为保存工作簿时使用的范围的标准，以免丢失部分工作。

3. Used =“...正在使用，表示包含数据或格式”或条件格式的部分。
   与 2. 相同，但还包括作为任何条件格式规则目标的单元格。

如何查找上次使用的单元格取决于您想要什么（您的标准）。

对于标准 1，我建议阅读此答案。
请注意，UsedRange 被认为是不可靠的。我认为这是误导性的（即对 UsedRange 不公平），因为 UsedRange 根本不意味着报告包含数据的最后一个单元格。因此，如该答案所示，在这种情况下不应使用它。另请参阅此评论。

对于标准 2，与也为此用途设计的其他选项相比，UsedRange 是最可靠的选项。它甚至不需要保存工作簿来确保更新最后一个单元格。
Ctrl+End 将在保存之前转到错误的单元格
（“在保存工作表之前，最后一个单元格不会重置”，来自
http://msdn.microsoft.com/en -us/library/aa139976%28v=office.10%29.aspx。
这是一个古老的参考文献，但在这方面是有效的）。

对于标准 3，我不知道任何内置方法。
标准 2 不考虑条件格式。可能有基于公式的格式化单元格，UsedRange 或 Ctrl+End 无法检测到这些单元格。
在图中，最后一个单元格是 B3，因为格式已显式应用于它。单元格 B6:D7 具有源自条件格式规则的格式，即使 UsedRange 也无法检测到这一点。
考虑到这一点需要一些 VBA 编程。

---

关于您的具体问题：
这背后的原因是什么？

您的代码使用范围 E4:E48 中的第一个单元格作为蹦床，通过 End(xlDown)跳跃 >。

如果您的范围内除了第一个单元格之外没有非空白单元格，则会获得“错误”输出。然后，您在黑暗中跳跃，即沿着工作表向下跳跃
（您应该注意空白和空字符串之间的区别！）。

请注意：

1. 如果您的范围包含不连续的非空白单元格，那么它也会给出错误的结果。

2. 如果只有一个非空白单元格，但它不是第一个，您的代码仍然会给出正确的结果。

Answer 3

我创建了这个一站式函数来确定最后一行、最后一列和单元格，无论是数据、格式化（分组/注释/隐藏）单元格还是条件格式。

Sub LastCellMsg()
    Dim strResult As String
    Dim lngDataRow As Long
    Dim lngDataCol As Long
    Dim strDataCell As String
    Dim strDataFormatRow As String
    Dim lngDataFormatCol As Long
    Dim strDataFormatCell As String
    Dim oFormatCond As FormatCondition
    Dim lngTempRow As Long
    Dim lngTempCol As Long
    Dim lngCFRow As Long
    Dim lngCFCol As Long
    Dim strCFCell As String
    Dim lngOverallRow As Long
    Dim lngOverallCol As Long
    Dim strOverallCell As String

    With ActiveSheet

        If .ListObjects.Count > 0 Then
            MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
            Exit Sub
        End If

        strResult = "Workbook name: " & .Parent.Name & vbCrLf
        strResult = strResult & "Sheet name: " & .Name & vbCrLf

        'DATA:
        'last data row
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataRow = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Row
        Else
            lngDataRow = 1
        End If
        'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf

        'last data column
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataCol = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByColumns, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Column
        Else
            lngDataCol = 1
        End If
        'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf

        'last data cell
        strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString)
        strResult = strResult & "Last data cell: " & strDataCell & vbCrLf

        'FORMATS:
        'last data/formatted/grouped/commented/hidden row
        strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0))
        'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf

        'last data/formatted/grouped/commented/hidden column
        lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column
        'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf

        'last data/formatted/grouped/commented/hidden cell
        strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString)
        strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf

        'CONDITIONAL FORMATS:
        For Each oFormatCond In .Cells.FormatConditions

            'last conditionally-formatted row
            lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0)))
            If lngTempRow > lngCFRow Then lngCFRow = lngTempRow

            'last conditionally-formatted column
            lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column
            If lngTempCol > lngCFCol Then lngCFCol = lngTempCol
        Next
        'no results are returned for Conditional Format if there is no such
        If lngCFRow <> 0 Then
            'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf
            'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf

            'last conditionally-formatted cell
            strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString)
            strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf
        End If

        'OVERALL:
        lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow)
        'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf
        lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol)
        'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf
        strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString)
        strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf

        MsgBox strResult
        Debug.Print strResult

    End With

End Sub

结果如下所示：

要获得更详细的结果，可以取消注释代码中的某些行：

存在一个限制 - 如果工作表中有表格，结果可能会变得不可靠，因此我决定避免在这种情况下运行代码：

If .ListObjects.Count > 0 Then
    MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
    Exit Sub
End If

Answer 4

使用解决方案时要记住的一个重要注意事项......

LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

是确保您的 LastRow 变量是 Long 类型：

Dim LastRow as Long

否则您最终会得到 OVERFLOW .XLSX 工作簿中某些情况下的错误

这是我的封装函数，我将其放入各种代码使用中。

Private Function FindLastRow(ws As Worksheet) As Long
    ' --------------------------------------------------------------------------------
    ' Find the last used Row on a Worksheet
    ' --------------------------------------------------------------------------------
    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        ' Search for any entry, by searching backwards by Rows.
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End Function

Answer 5

由于最初的问题是关于查找最后一个单元格的问题，在这个答案中我将列出可以获得意外结果的各种方法；请参阅我对“如何使用宏找到 Excel 工作表中包含数据的最后一行？”的回答 我对解决这个问题的看法。

我将首先扩展桑乔的答案。 s 和 GlennFromIowa 的评论，添加了更多详细信息：

[...]首先要决定什么被认为是使用的。我看到至少有6层含义。细胞有：

• 1) 数据，即公式，可能会产生空白值；
• 2）一个值，即非空公式或常数；
• 3）格式化；
• 4) 条件格式；
• 5) 与单元格重叠的形状（包括注释）；
• 6) 涉及表（列表对象）。

您想测试哪种组合？有些（例如表格）可能更难以测试，有些可能很少见（例如数据范围之外的形状），但其他可能会根据情况而变化（例如带有空白值的公式）。

您可能需要考虑的其他事项：

• A) 是否可以有隐藏行（例如自动筛选器）、空白单元格或空白行？
• B) 什么样的表现是可以接受的？
• C) VBA 宏是否会以任何方式影响工作簿或应用程序设置？

考虑到这一点，让我们看看获取“最后一个单元格”的常见方法如何产生意想不到的结果：

• 问题中的.End(xlDown)代码将破坏大多数内容很容易（例如单个非空单元格或中间有空白单元格），原因在Siddharth Rout 的答案在这里（搜索 < em>“xlDown 同样不可靠。”)
  

Answer 6

我想知道没有人提到这一点，但获取最后使用的单元格的最简单方法是：

Function GetLastCell(sh as Worksheet) As Range
    GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell)
End Function

这本质上返回与选择单元格后通过 Ctrl + End 获得的相同单元格A1。

警告：Excel 会跟踪工作表中曾经使用过的最右下角的单元格。例如，如果您在 B3 中输入某些内容，在 H8 中输入其他内容，然后删除 H8 的内容，请按 Ctrl + End 仍会将您带到 H8 单元格。上述函数将具有相同的行为。

Answer 7

我想在 Siddarth Rout 给出的答案中添加一点，即可以通过让 Find 返回 Range 对象而不是行号来跳过 CountA 调用，然后测试返回的 Range 对象以查看它是否为 Nothing（空白工作表） 。

另外，我会让任何 LastRow 过程的版本为空白工作表返回零，然后我就可以知道它是空白的。

Answer 8

于 2021 年底更新，

使用 Excel 的新 计算引擎和数组功能，以及 过滤功能，我相信这个现在这个话题的争议应该少得多，并且以下选项提供了速度、可靠性和简单性的最佳组合（正如这里的众多帖子所说明的那样，过去已经证明很难平衡这一点）。

另外，我将最后使用定义为非空白，如isBlank 函数。

Excel 公式

首先，请注意，筛选函数可以更轻松地使用以下公式获取特定行或列的最后一个单元格（在这些情况下为 Column A 或 Row 1):

=MAX(FILTER(ROW(A:A),NOT(ISBLANK(A:A))))
=MAX(FILTER(COLUMN(1:1),NOT(ISBLANK(1:1))))

最后一行特定范围的 VBA 函数

使用上述函数，我们可以将其转换为 VBA 函数，但通过限制范围使其速度更快，同时通过执行多列扩展其功能（感谢 Chris Neilsen 获取即时反馈调整/建议）。我还发现，通过将每一列的范围限定为一个比上一个最后一行更高的行的范围，速度得到了巨大的提高。

Function FindLastRowInRange(someColumns As Range) As Long
Const zFx = "=MAX(FILTER(ROW(????),NOT(ISBLANK(????)),0))"
   
   Dim tRng As Range, i As Long, tRow As Long, pRng As Range
   With someColumns.Worksheet
      Set tRng = Intersect(someColumns.EntireColumn, .UsedRange)
      
      For i = 1 To tRng.Columns.Count
         
         Set pRng = Intersect(tRng.Columns(i), _
         Range(.Rows(FindLastRowInRange + 1), .Rows(.Rows.Count)))
         
         If Not pRng Is Nothing Then
            tRow = .Evaluate(Replace(zFx, "????", _
               pRng.Address, 1, -1))
         
            If tRow > FindLastRowInRange Then _
               FindLastRowInRange = tRow
            
         End If
      Next i
   End With
End Function

工作表中最后一行的 VBA 函数

要考虑整个工作表（所有列），我建议使用引用前一个公式的不同 VBA 公式，但它是一个 易失性函数。这可确保公式随着工作表的任何更改而更新。显然，可以将这两个公式结合起来，但我更喜欢限制易失性函数的使用。

Function FindLastRowInSheet(anywhereInSheet As Range) As Long
      Application.Volatile
      FindLastRowInSheet = FindLastRowInRange(anywhereInSheet.Worksheet.UsedRange)
End Function

与其他选项相比的优点

• 允许工作表中的部分或全部行/列，而无需更改方法。
• 不可能像 xlup 那样丢失隐藏行，
• 忽略格式化/使用范围问题。
• 不会干扰用户的Find 设置。
• 使用比 VBA 计算更快的工作表功能。
• 不计数细胞（性能消耗）。

希望这场辩论就此结束，但如果有人发现其中的弱点，请分享。

Answer 9

我一直在寻找一种模仿 CTRL+Shift+End 的方法，所以 dotNET 解决方案很棒，除了我的 Excel 2010 需要如果我想避免错误，请添加 set：

Function GetLastCell(sh As Worksheet) As Range
  Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell)
End Function

以及如何自行检查：

Sub test()
  Dim ws As Worksheet, r As Range
  Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set r = GetLastCell(ws)
  MsgBox r.Column & "-" & r.Row
End Sub

Answer 10

sub last_filled_cell()
msgbox range("A65536").end(xlup).row
end sub

这里，A65536 是 A 列中的最后一个单元格，此代码在 Excel 2003 上进行了测试。

Answer 11

然而，这个问题正在寻求使用 VBA 查找最后一行，我认为最好包含工作表函数的数组公式，因为它会被频繁访问：

{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}

您需要输入不带括号的公式，然后按 Shift + Ctrl + Enter 使其成为数组公式。

这将为您提供 D 列中最后使用的单元格的地址。

---

感谢 pgsystemtester，这将为您提供最后使用的单元格的行号：

{=MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0)}

Answer 12

Sub lastRow()

    Dim i As Long
        i = Cells(Rows.Count, 1).End(xlUp).Row
            MsgBox i

End Sub

sub LastRow()

'Paste & for better understanding of the working use F8 Key to run the code .

dim WS as worksheet
dim i as long

set ws = thisworkbook("SheetName")

ws.activate

ws.range("a1").select

ws.range("a1048576").select

activecell.end(xlup).select

i= activecell.row

msgbox "My Last Row Is " & i

End sub

Answer 13

在过去的 3 年多里，这些是我用来查找每个定义的列（对于行）和行（对于列）的最后一行和最后一列的函数：

最后一列：

Function lastCol(Optional wsName As String, Optional rowToCheck As Long = 1) As Long

    Dim ws  As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastCol = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function

最后一行：

Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

对于 OP 的情况，这是获取E列最后一行的方法：

Debug.Print lastRow(columnToCheck:=Range("E4:E48").Column)

Last Row，计算空行有数据：

在这里我们可以使用 众所周知的 Excel 公式，它为我们提供了 Excel 中工作表的最后一行，而不涉及 VBA - =IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0)

为了将其放入 VBA 中而不是编写任何内容在 Excel 中，使用后一个函数的参数，可以考虑这样的事情：

Public Function LastRowWithHidden(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    Dim letters As String
    letters = ColLettersGenerator(columnToCheck)
    LastRowWithHidden = ws.Evaluate("=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(" & letters & "))),ROW(" & letters & " )),0)")

End Function

Function ColLettersGenerator(col As Long) As String

    Dim result As Variant
    result = Split(Cells(1, col).Address(True, False), "$")
    ColLettersGenerator = result(0) & ":" & result(0)

End Function

Answer 14

常规范围或表（列表对象）中的最后一行

1. 如果范围是常规范围或表（列表对象），则查找最后一行需要使用不同的方法。
2. 查找表中的最后一行需要指定其他参数（表名称、列相对于表第一个列的位置）。

无论范围类型如何，我都为最后一行创建了这个通用函数。只要给它任何单元格引用，它就会返回最后一行。
不必知道范围特征，特别是如果您的范围有时是常规范围，有时是 ListObject，则没有麻烦。
在表上使用常规范围方法可能会返回错误的结果。
当然，您可以提前计划并每次都使用正确的方法，但如果您可以利用通用功能，为什么还要麻烦呢？

Sub RunMyLastRow()
结果暗淡只要
结果 = MyLastRow(工作表(1).Range("A1"))
结束子

    Function MyLastRow(RefrenceRange As Range) As Long
    Dim WS As Worksheet
    Dim TableName As String
    Dim ColNumber As Long
    Dim LastRow As Long
    Dim FirstColumnTable As Long
    Dim ColNumberTable As Long
    Set WS = RefrenceRange.Worksheet
    TableName = GetTableName(RefrenceRange)
    ColNumber = RefrenceRange.Column
    
    ''If the table (ListObject) does not start in column "A" we need to calculate the 
    ''first Column table and how many Columns from its beginning the Column is located.
    If TableName <> vbNullString Then
     FirstColumnTable = WS.ListObjects(TableName).ListColumns(1).Range.Column
     ColNumberTable = ColNumber - FirstColumnTable + 1
    End If 

    If TableName = vbNullString Then
    LastRow = WS.Cells(WS.Rows.Count, ColNumber).End(xlUp).Row
    Else
    LastRow = WS.ListObjects(TableName).ListColumns(ColNumberTable).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    MyLastRow = LastRow
    End Function
    

 ''通过单元格范围获取表名称
    函数 GetTableName(RefrenceRange As Range) As String
        如果RefrenceRange.ListObject什么都没有那么
            获取表名称 = vbNullString
        别的
            GetTableName = RefrenceRange.ListObject.Name
        结束如果
    结束功能