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

Error in finding last used cell in Excel with VBA

提问by james

When I want to find the last used cell value, I use:

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

Dim LastRow As Long

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

Debug.Print LastRow

I am getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. What's the reason behind this?

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

回答by Siddharth Rout

NOTE: I intend to make this a "one stop post" where you can use the Correctway to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.

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

---

---

Unreliable ways of finding the last row

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

Some of the most common ways of finding last row which are highly unreliable and hence should never be used.

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

1. UsedRange
2. xlDown
3. CountA

1. 使用范围
2. 向下
3. 计数A

UsedRangeshould NEVERbe used to find the last cell which has data. It is highly unreliable. Try this experiment.

UsedRange应该从来没有被用来寻找具有数据的最后一个单元格。这是非常不可靠的。试试这个实验。

Type something in cell A5. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell A10red. If you now use the any of the below code, you will still get 5. If you use Usedrange.Rows.Countwhat do you get? It won't be 5.

在 cell 中输入一些东西A5。现在，当您使用下面给出的任何方法计算最后一行时，它将为您提供 5。现在将单元格着色为A10红色。如果您现在使用以下任何代码，您仍将获得 5。如果您使用，您将获得Usedrange.Rows.Count什么？不会是5

Here is a scenario to show how UsedRangeworks.

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

xlDownis equally unreliable.

xlDown同样不可靠。

Consider this code

考虑这个代码

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

What would happen if there was only one cell (A1) which had data? You will end up reaching the last row in the worksheet! It's like selecting cell A1and then pressing Endkey and then pressing Down Arrowkey. This will also give you unreliable results if there are blank cells in a range.

如果只有一个单元格 ( A1) 有数据会发生什么？您最终将到达工作表中的最后一行！这就像选择单元格A1然后End按键然后Down Arrow按键。如果区域中有空白单元格，这也会给您不可靠的结果。

CountAis also unreliable because it will give you incorrect result if there are blank cells in between.

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

And hence one should avoid the use of UsedRange, xlDownand CountAto find the last cell.

因此应该避免使用UsedRange,xlDown并CountA找到最后一个单元格。

---

---

Find Last Row in a Column

查找列中的最后一行

To find the last Row in Col E use this

要找到 Col E 中的最后一行，请使用此

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

If you notice that we have a .before Rows.Count. We often chose to ignore that. See THISquestion on the possible error that you may get. I always advise using .before Rows.Countand Columns.Count. That question is a classic scenario where the code will fail because the Rows.Countreturns 65536for Excel 2003 and earlier and 1048576for Excel 2007 and later. Similarly Columns.Countreturns 256and 16384, respectively.

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

The above fact that Excel 2007+ has 1048576rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Longinstead of Integerelse you will get an Overflowerror.

以上事实的Excel 2007+具有1048576行也强调这一事实，我们应该始终宣布将举行的行值作为变量Long，而不是Integer其他人，你会得到一个Overflow错误。

Note that this approach will skip any hidden rows. Looking back at my screenshot above for column A, if row 8 were hidden, this approach would return 5instead of 8.

请注意，此方法将跳过任何隐藏的行。回顾上面 A 列的截图，如果第 8 行被隐藏，这种方法将返回5而不是8.

---

---

Find Last Row in a Sheet

查找工作表中的最后一行

To find the Effectivelast row in the sheet, use this. Notice the use of Application.WorksheetFunction.CountA(.Cells). This is required because if there are no cells with data in the worksheet then .Findwill give you Run Time Error 91: Object Variable or With block variable not set

要查找工作表中的Effective最后一行，请使用它。注意使用Application.WorksheetFunction.CountA(.Cells). 这是必需的，因为如果工作表中没有包含数据的单元格，那么.Find会给你Run Time Error 91: Object Variable or With block variable not set

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 in a Table (ListObject)

查找表中的最后一行 (ListObject)

The same principles apply, for example to get the last row in the third column of a table:

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

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

回答by sancho.s ReinstateMonicaCellio

Note: this answer was motivated by this comment. The purpose of UsedRangeis different from what is mentioned in the answer above.

注意：这个答案是由这个评论激发的。的目的与UsedRange上面答案中提到的不同。

As to the correct way of finding the last used cell, one has first to decide what is considered used, and then select a suitable method. I conceive at least three meanings:

至于找到最后使用的单元格的正确方法，首先要决定什么被认为是使用的，然后选择合适的方法。我认为至少有三个含义：

1. Used = non-blank, i.e., having data.

2. Used = "... in use, meaning the section that contains data or formatting." As per official documentation, this is the criterion used by Excel at the time of saving. See also this official documentation. If one is not aware of this, the criterion may produce unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook, lest losing part of one's work.

3. Used = "... in use, meaning the section that contains data or formatting" or conditional formatting.Same as 2., but also including cells that are the target for any Conditional Formatting rule.

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

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

3. Used = "... in use，意思是包含数据或格式的部分"或条件格式。与 2. 相同，但还包括作为任何条件格式规则目标的单元格。

How to find the last used cell depends on what youwant (your criterion).

如何找到最后使用的单元格取决于您想要什么（您的标准）。

For criterion 1, I suggest reading this answer. Note that UsedRangeis cited as unreliable. I think that is misleading (i.e., "unfair" to UsedRange), as UsedRangeis simply not meant to report the last cell containing data. So it should not be used in this case, as indicated in that answer. See also this comment.

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

For criterion 2, UsedRangeis the most reliable option, as compared to other options also designed for this use. It even makes it unnecessary to save a workbook to make sure that the last cell is updated. Ctrl+Endwill go to a wrong cell prior to saving (“The last cell is not reset until you save the worksheet”, from http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx. It is an old reference, but in this respect valid).

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

For criterion 3, I do not know any built-in method. Criterion 2 does not account for Conditional Formatting. One may have formatted cells, based on formulas, which are not detected by UsedRangeor Ctrl+End. In the figure, the last cell is B3, since formatting was applied explicitly to it. Cells B6:D7 have a format derived from a Conditional Formatting rule, and this is not detected even by UsedRange. Accounting for this would require some VBA programming.

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

---

---

As to your specific question: What's the reason behind this?

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

Your code uses the first cell in your range E4:E48 as a trampoline, for jumpingdown with End(xlDown).

您的代码使用的第一个单元格在你的范围E4：E48作为一个蹦床，对于跳跃下来End(xlDown)。

The "erroneous" output will obtain if there are no non-blankcells in your range other than perhaps the first. Then, you are leaping in the dark, i.e., down the worksheet (you should note the difference between blankand empty string!).

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

Note that:

注意：

1. If your range contains non-contiguous non-blank cells, then it will also give a wrong result.

2. If there is only one non-blank cell, but it is not the first one, your code will still give you the correct result.

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

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

回答by ZygD

I created this one-stop function for determining the last row, column and cell, be it for data, formatted (grouped/commented/hidden) cells or conditional formatting.

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

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

Results look like this:

结果如下所示：

For more detailed results, some lines in the code can be uncommented:

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

One limitation exists - if there are tables in the sheet, results can become unreliable, so I decided to avoid running the code in this case:

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

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

回答by Bishop

One important note to keep in mind when using the solution ...

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

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

... is to ensure that your LastRowvariable is of Longtype:

... 是为了确保您的LastRow变量属于以下Long类型：

Dim LastRow as Long

Otherwise you will end up getting OVERFLOW errors in certain situations in .XLSX workbooks

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

This is my encapsulated function that I drop in to various code uses.

这是我用于各种代码用途的封装函数。

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

回答by dotNET

I wonder that nobody has mentioned this, But the easiest way of getting the last used cell is:

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

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

This essentially returns the same cell that you get by Ctrl+ Endafter selecting Cell A1.

这基本上返回了您在选择 Cell 后通过Ctrl+获得的相同单元End格A1。

A word of caution: Excel keeps track of the most bottom-right cell that was ever used in a worksheet. So if for example you enter something in B3and something else in H8and then later on delete the contents of H8, pressing Ctrl+ Endwill still take you to H8cell. The above function will have the same behavior.

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

回答by no comprende

I would add to the answer given by Siddarth Rout to say that the CountA call can be skipped by having Find return a Range object, instead of a row number, and then test the returned Range object to see if it is Nothing (blank worksheet).

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

Also, I would have my version of any LastRow procedure return a zero for a blank worksheet, then I can know it is blank.

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

回答by Nickolay

Since the original question is about problemswith finding the last cell, in this answer I will list the various ways you can get unexpected results; see my answer to "How can I find last row that contains data in the Excel sheet with a macro?"for my take on solving this.

由于原来的问题是关于问题与找到的最后一个单元格，在这个答案我会列出你可以得到意想不到的效果的各种方法; 请参阅我对“如何使用宏在 Excel 工作表中找到包含数据的最后一行？”的回答。我对解决这个问题的看法。

I'll start by expanding on the answer by sancho.sand the comment by GlennFromIowa, adding even more detail:

我会通过扩展开始由sancho.s答案，并通过GlennFromIowa注释，增加了更多的细节：

[...] one has first to decide what is considered used. I see at least 6 meanings. Cell has:

• 1) data, i.e., a formula, possibly resulting in a blank value;
• 2) a value, i.e., a non-blank formula or constant;
• 3) formatting;
• 4) conditional formatting;
• 5) a shape (including Comment) overlapping the cell;
• 6) involvement in a Table (List Object).

Which combination do you want to test for? Some (such as Tables) may be more difficult to test for, and some may be rare (such as a shape outside of data range), but others may vary based on the situation (e.g., formulas with blank values).

[...] 首先要决定什么被认为是使用的。我看到至少有6个含义。单元格具有：

• 1) 数据，即公式，可能导致空白值；
• 2) 一个值，即一个非空的公式或常数；
• 3) 格式化；
• 4) 条件格式；
• 5）与单元格重叠的形状（包括Comment）；
• 6) 参与一个表（List Object）。

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

Other things you might want to consider:

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

• A) Can there be hidden rows(e.g. autofilter), blank cellsor blank rows?
• B) What kind of performance is acceptable?
• C) Can the VBA macro affect the workbook or the application settings in any way?

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

With that in mind, let's see how the common ways of getting the "last cell" can produce unexpected results:

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

• The .End(xlDown)code from the question will break most easily (e.g. with a single non-empty cellor when there are blank cells in between) for the reasons explained in the answer by Siddharth Routhere (search for "xlDown is equally unreliable.")
• Any solution based on Counting (CountAor Cells*.Count) or .CurrentRegionwill also break in presence of blank cells or rows
• A solution involving .End(xlUp)to search backwards from the end of a column will, just as CTRL+UP, look for data(formulas producing a blank value are considered "data") in visible rows(so using it with autofilter enabled might produce incorrect results ??).

  You have to take care to avoid the standard pitfalls (for details I'll again refer to the answer by Siddharth Routhere, look for the "Find Last Row in a Column"section), such as hard-coding the last row (Range("A65536").End(xlUp)) instead of relying on sht.Rows.Count.

• .SpecialCells(xlLastCell)is equivalent to CTRL+END, returning the bottom-most and right-most cell of the "used range", so all caveats that apply to relying on the "used range", apply to this method as well. In addition, the "used range" is only reset when saving the workbook and when accessing worksheet.UsedRange, so xlLastCellmight produce stale results?? with unsaved modifications (e.g. after some rows were deleted). See the nearby answer by dotNET.
• sht.UsedRange(described in detail in the answer by sancho.shere) considers both data and formatting(though not conditional formatting) and resets the "used range" of the worksheet, which may or may not be what you want.

  Note that a common mistake ?is to use .UsedRange.Rows.Count??, which returns the number of rowsin the used range, not the last row number(they will be different if the first few rows are blank), for details see newguy's answer to How can I find last row that contains data in the Excel sheet with a macro?

• .Findallows you to find the last row with any data (including formulas) or a non-blank value in any column. You can choose whether you're interested in formulas or values, but the catch is that it resets the defaults in the Excel's Find dialog????, which can be highly confusing to your users. It also needs to be used carefully, see the answer by Siddharth Routhere (section "Find Last Row in a Sheet")
• More explicit solutions that check individual Cells' in a loop are generally slower than re-using an Excel function (although can still be performant), but let you specify exactly what you want to find. See my solutionbased on UsedRangeand VBA arrays to find the last cell with data in the given column -- it handles hidden rows, filters, blanks, does not modify the Find defaults and is quite performant.

• 由于Siddharth Rout在这里的回答中解释的原因（搜索“xlDown 同样不可靠。”）.End(xlDown)，问题中的代码最容易损坏（例如，使用单个非空单元格或中间有空白单元格时）。
• 任何基于Counting (CountA或Cells*.Count) or 的解决方案.CurrentRegion也会在存在空白单元格或行时中断
• 涉及.End(xlUp)从列末尾向后搜索的解决方案将与 CTRL+UP 一样，在可见行中查找数据（产生空白值的公式被视为“数据”）（因此在启用自动过滤器的情况下使用它可能会产生不正确的结果？ ？）。

  您必须小心避免标准陷阱（有关详细信息，我将再次参考Siddharth Rout在这里的答案，查找“在列中查找最后一行”部分），例如对最后一行进行硬编码 ( Range("A65536").End(xlUp))而不是依赖sht.Rows.Count。

• .SpecialCells(xlLastCell)等效于 CTRL+END，返回“使用范围”的最底部和最右侧的单元格，因此适用于依赖“使用范围”的所有警告也适用于此方法。此外，“使用范围”仅在保存工作簿和访问时重置worksheet.UsedRange，因此xlLastCell可能会产生过时的结果？？带有未保存的修改（例如在删除某些行之后）。请参阅dotNET 附近的答案。
• sht.UsedRange（在sancho.shere的答案中详细描述）考虑数据和格式（尽管不是条件格式）并重置工作表的“使用范围”，这可能是您想要的，也可能不是。

  需要注意的是一个常见的错误？是使用.UsedRange.Rows.Count??，它返回的行数的使用范围，而不是最后的行号（他们会有所不同，如果前几行是空白），详见newguy的答案怎么能我找到包含 Excel 工作表中带有宏的数据的最后一行？

• .Find允许您在任何列中查找包含任何数据（包括公式）或非空值的最后一行。您可以选择是对公式还是值感兴趣，但问题是它会重置 Excel 的“查找”对话框中的默认值？？？？，这可能会让您的用户感到非常困惑。它还需要谨慎使用，请参阅Siddharth Rout 的答案（“在表格中查找最后一行”部分）
• Cells在循环中检查单个' 的更明确的解决方案通常比重新使用 Excel 函数慢（尽管仍然可以执行），但可以让您准确指定要查找的内容。请参阅我的基于UsedRangeVBA 数组的解决方案，以查找给定列中包含数据的最后一个单元格——它处理隐藏的行、过滤器、空白，不修改查找默认值并且非常高效。

Whatever solution you pick, be careful

无论你选择什么解决方案，都要小心

• to use Longinstead of Integerto store the row numbers (to avoid getting Overflowwith more than 65k rows) and
• to always specify the worksheet you're working with (i.e. Dim ws As Worksheet ... ws.Range(...)instead of Range(...))
• when using .Value(which is a Variant) avoid implicit casts like .Value <> ""as they will fail if the cell contains an error value.

• 使用Long而不是Integer存储行号（以避免获得Overflow超过 65k 行）和
• 始终指定您正在使用的工作表（即Dim ws As Worksheet ... ws.Range(...)代替Range(...)）
• 使用时.Value（这是 a Variant）避免隐式强制转换，.Value <> ""因为如果单元格包含错误值，它们将失败。

回答by M--

However this question is seeking to find the last row using VBA, I think it would be good to include an array formula for worksheet function as this gets visited frequently:

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

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

You need to enter the formula without brackets and then hit Shift+ Ctrl+ Enterto make it an array formula.

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

This will give you address of last used cell in the column D.

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

回答by Ashwith Ullal

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

Here, A65536is the last cell in the Column A this code was tested on excel 2003.

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

回答by J. Chomel

I was looking for a way to mimic the CTRL+Shift+End, so dotNET solution is great, except with my Excel 2010 I need to add a setif I want to avoid an error:

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

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

and how to check this for yourself:

以及如何自己检查：

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