Excel.Application.Cells.SpecialCells（xlCellTypeLastCell）返回工作表底部，而不是最后的数据单元格 [英] Excel.Application.Cells.SpecialCells(xlCellTypeLastCell) returning bottom of worksheet, not last data cell

问题描述

我在Excel 2013中在VBA中编写一个方法来循环遍历两个工作表中的行，并比较每个列中的文本。但是，当我循环访问时，我发现代码循环遍历整个工作表，而不仅仅是数据行。 Excel.Sheets（Sheet1）。Cells.SpecialCells（xlCellTypeLastCell）返回正确列中的单元格，但该行是表格（1048576）中的最后一行，而不是最后一行数据（1951）。我写了一个空的单元格检查代码（因为我不能确定每一行都被使用），所以它不会造成问题，这只是意味着每次运行它需要很长的时间这应该。而且由于这个方法是从Worksheet_Change里面调用的，所以真的会减慢事情的发生。

通常，当最后单元格报错时，我会发现一个保存通常会修复它，如果没有，那么从错误报告的最后一个单元格删除行（不仅仅是内容，而是整个行）返回到最后一个单元格，然后然后保存作品。不过，在这种情况下，这并没有帮助。

我没有成功通过Google查看。我想不需要将所有的数据和代码从本工作簿复制到一个新的，如果我可以帮助它。任何人都有任何建议？

解决方案

（太多的信息可以在这里使用评论。）

VBA主管罗恩·德布鲁恩写了一个小小的关于为什么 xlCellTypeLastCell 以及 UsedRange 可能会在这里失败： http://www.rondebruin.nl/win/s9/win005.htm

（在我的初始评论中链接到的帖子中，在VBA中查找最后使用的单元格中出错， UsedRange 的陷阱以相同的方式描述。）

这是直接引用：

xlCellTypeLastCell和UsedRange可能出现的问题是：

当您保存（或保存/关闭/重新打开
文件）时，最后一个单元格将仅重新设置。如果单元格格式更改，将不会重置最后一个单元格，
清除数据不够，您必须删除行或列
然后，请参阅： http://www.contextures.com/xlfaqApp.html#Unused

为了简短说明一个简短的故事，用于查找工作表上最后一行的逻辑属于全局函数。您将始终使用此功能。以下是一个例子，用于查找工作表上的最后一行：

 ''''''''''''''  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''$' b $ b'特殊情况：如果Sheet为空，则返回1 
'示例：
'
'假设单元格A5中的MySheet上有一个
'条目：
'
'LastRowNum（MySheet）
'>> 5 
'
'假设EmptySheet是空的：
'
'LastRowNum（EmptySheet）
'>> 1 
'
公共功能LastRowNum（Sheet As Worksheet）As Long 
如果Application.WorksheetFunction.CountA（Sheet.Cells）<> 0然后
 LastRowNum = Sheet.Cells.Find（什么：=*，_ 
 LookIn：= xlFormulas，_ 
 SearchOrder：= xlByRows，_ 
 SearchDirection：= xlPrevious ）.Row 
 Else 
 LastRowNum = 1 
 End If 
 End Function 
  

就downvote而言 - 不知道。我实际上从来没有在这里哈哈哈哈。

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. However, when I loop through, I'm finding that the code is looping through the entire worksheet, not just the rows with data. 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). I've written a check for empty cells into the code (since I can't be sure that every row is used), so it doesn't cause a problem, it just means that every time it runs it takes a lot longer than it should. And as this method is being called from inside Worksheet_Change, it really slows things down.

Usually, when the "last" cell is being reported incorrectly, I'd find that 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 then saving works. However, in this instance, it's not helping.

I've looked through 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 if I can help it. Anyone have any suggestions?

解决方案

(Too much info to use a comment here.)

VBA mastermind Ron de Bruin wrote a little snipped about why xlCellTypeLastCell as well as UsedRange might be failing here: 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 UsedRange are described in the same way.)

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

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.

这篇关于Excel.Application.Cells.SpecialCells（xlCellTypeLastCell）返回工作表底部，而不是最后的数据单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！