在VBA中查找最后使用的单元格时出错 [英] Error in finding last used cell in VBA

问题描述

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

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

Dim last_row As Integer
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?

推荐答案

注意：我打算让这个一站式，您可以使用正确的方式查找最后一行。这也将涵盖找到最后一行时遵循的最佳做法。因此，当我遇到新的情景/信息时，我会继续更新。

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


2. xlDown


3. CountA

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

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 ，然后按结束键，然后按向下箭头键。如果空白单元格在一个范围内，这也将给您带来不可靠的结果。

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 。我们经常选择忽略。有关可能出现的错误，请参见此问题。我总是建议使用。 before Rows.Count 和 Columns.Count 。这个问题是一个典型的情况，其中代码将失败，因为Excel 2003及更早版本的 Rows.Count 返回 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 而不是整数否则您将获得 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.

要查找工作表中的有效最后一行，请使用此选项。注意使用 Application.WorksheetFunction.CountA（.Cells）。这是必需的，因为如果没有工作表中的数据单元格，那么 .Find 将为您提供运行时错误91：对象变量或块变量没有设置

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

---

< h2>查找表中的最后一行（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中查找最后使用的单元格时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！