使用 VBA 在 Excel 中查找上次使用的单元格时出错 [英] Error in finding last used cell in Excel with VBA

问题描述

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

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'm 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?

推荐答案

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

NOTE: I intend to make this a "one stop post" where you can use the Correct way 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.

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

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

1. 使用范围
2. xlDown
3. 计数A

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

UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment.

在单元格 A5 中键入内容.现在，当您使用下面给出的任何方法计算最后一行时，它将为您提供 5.现在将单元格 A10 着色为红色.如果你现在使用下面的任何代码，你仍然会得到 5.如果你使用 Usedrange.Rows.Count 你会得到什么?不会是 5.

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 A10 red. If you now use the any of the below code, you will still get 5. If you use Usedrange.Rows.Count what do you get? It won't be 5.

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

Here is a scenario to show how UsedRange works.

xlDown 同样不可靠.

考虑这个代码

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

如果只有一个单元格(A1)有数据会怎样?您最终将到达工作表中的最后一行！这就像选择单元格 A1 然后按 End 键，然后按 向下箭头 键.如果区域中有空白单元格，这也会给您带来不可靠的结果.

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 A1 and then pressing End key and then pressing Down Arrow key. This will also give you unreliable results if there are blank cells in a range.

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

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

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

And hence one should avoid the use of UsedRange, xlDown and CountA to find the last cell.

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

To find the last Row in Col E use this

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.

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

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

The above fact that Excel 2007+ has 1048576 rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long instead of Integer else you will get an Overflow error.

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

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 5 instead of 8.

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

To find the Effective last 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 .Find will give you 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

<小时>

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

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

---

Find Last Row in a Table (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

这篇关于使用 VBA 在 Excel 中查找上次使用的单元格时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！