使用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 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?

推荐答案

注意:我打算将其设为一站式发布"，您可以在其中使用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. 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，然后按 End 键，然后按 Down Arrow 键一样.如果范围中有空白单元格，这也会给您带来不可靠的结果.

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屋！