vba Excel.Application.Cells.SpecialCells(xlCellTypeLastCell) 返回工作表的底部，而不是最后一个数据单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25110873/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel.Application.Cells.SpecialCells(xlCellTypeLastCell) returning bottom of worksheet, not last data cell
提问by DarkMoon
I'm writing a method in VBA in Excel 2013 to loop through the rows in two worksheets and compare the text in a column from each. When I run my code, I find that the code loops through the entire worksheet, not just the rows with data.
我正在 Excel 2013 中的 VBA 中编写一种方法来循环遍历两个工作表中的行并比较每个工作表中一列中的文本。当我运行我的代码时，我发现代码循环遍历整个工作表，而不仅仅是包含数据的行。
Excel.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell)returns a cell in the correct column, but the row is the last row in the sheet (1048576), rather than the last row with data (1951).
Excel.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell)返回正确列中的单元格，但该行是工作表中的最后一行 (1048576)，而不是包含数据的最后一行 (1951)。
I had written a check for empty cells (since I can't be sure that every row in the valid range is used), so it doesn't cause any errors, but as this method is called from inside Worksheet_Change, it really slows things down.
我写了一个空单元格的检查（因为我不能确定有效范围内的每一行都被使用了），所以它不会导致任何错误，但是由于这个方法是从内部调用的Worksheet_Change，它确实减慢了速度.
Normally when the "last" cell is reported incorrectly a save usually fixes it, and if that doesn't, then deleting the rows (not just the contents, but the entire rows) from the mis-reported last cell back to the actual last cell and thensaving works. However, in this instance, it is not helping.
通常，当“最后一个”单元格被错误报告时，保存通常会修复它，如果没有，则从错误报告的最后一个单元格中删除行（不仅仅是内容，而是整个行）回到实际的最后一个单元格单元格，然后保存工作。然而，在这种情况下，它没有帮助。
I searched with Google without success. I'd like to not have to copy all the data and code out of this workbook and into a new one. Any ideas?
我用谷歌搜索没有成功。我不想将所有数据和代码从这个工作簿中复制到一个新的工作簿中。有任何想法吗？
回答by Dan Wagner
(Too much info to use a comment here.)
（信息太多，无法在此处使用评论。）
VBA mastermind Ron de Bruin wrote a little snipped about why xlCellTypeLastCellas well as UsedRangemight be failing here: http://www.rondebruin.nl/win/s9/win005.htm.
VBA 策划者罗恩·德·布鲁因 (Ron de Bruin) 写了一篇关于为什么xlCellTypeLastCell以及UsedRange可能在这里失败的简短文章：http: //www.rondebruin.nl/win/s9/win005.htm。
(In the post I linked to in my initial comment, Error in finding last used cell in VBA, the pitfalls of UsedRangeare described in the same way.)
（在我最初评论中链接到的帖子中，在 VBA 中查找最后使用的单元时出错UsedRange，以相同的方式描述了 的陷阱。）
Here's the direct quote:
这是直接引用：
Possible problems with xlCellTypeLastCell and UsedRange are:
The last cell will only re-set when you save (or save/close/reopen the file). If cell formatting is changed it will not reset the last cell, clearing the data is not enough, you must delete the rows or columns then, See: http://www.contextures.com/xlfaqApp.html#Unused
xlCellTypeLastCell 和 UsedRange 可能存在的问题是：
最后一个单元格只会在您保存（或保存/关闭/重新打开文件）时重新设置。如果单元格格式被更改，它不会重置最后一个单元格，清除数据是不够的，您必须删除行或列，然后，请参阅：http: //www.contextures.com/xlfaqApp.html#Unused
To make a long story short, the logic for finding the last row on a sheet belongs inside a global function. You will use this function all the time. Here's an example for finding the last row on a sheet:
长话短说，查找工作表上最后一行的逻辑属于全局函数。您将一直使用此功能。以下是查找工作表最后一行的示例：
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT : Sheet, the worksheet we'll search to find the last row
'OUTPUT : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
'EXAMPLES :
'
'assume that there is a single
'entry on MySheet in cell A5:
'
'LastRowNum(MySheet)
'>> 5
'
'assume that EmptySheet is totally empty:
'
'LastRowNum(EmptySheet)
'>> 1
'
Public Function LastRowNum(Sheet As Worksheet) As Long
If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
LastRowNum = Sheet.Cells.Find(What:="*", _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Else
LastRowNum = 1
End If
End Function
As far as the downvote goes -- no idea. I've actually never downvoted here haha.
至于否决票 - 不知道。我实际上从未在这里投票过，哈哈。
回答by Christopher Teoh
Try using the following code to determine the last row & column instead
尝试使用以下代码来确定最后一行和最后一列
Dim LastCol As Long
Dim LastRow As Long
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
回答by Torres Viejo
You can try this:
你可以试试这个：
Dim xlastcel As Range
On Error Resume Next
ActiveSheet.ShowAllData: Err.Clear ' Show All
ActiveSheet.UsedRange ' Ajust Vertical Scrool
With ActiveSheet: Set xlastcell = .Cells.Find(What:="*", After:=.[A1], SearchDirection:=xlPrevious): End With
If Err.Number > 0 Then MsgBox "Sheet without data", vbOKOnly
On Error GoTo 0
