xlCellTypeLastCell给出不正确的值 [英] xlCellTypeLastCell giving incorect value

问题描述

在Excel VBA中，从 SpecialCells（xlCellTypeLastCell）中获取准确的值很难。以下代码应输出TrackedItems工作表中第一个空行的行号。工作表有8行，但代码输出25（应输出9）：

I'm having difficulty getting an accurate value from SpecialCells(xlCellTypeLastCell) in Excel VBA. The following code should output the row number for the first empty row in the "TrackedItems" worksheet. The worksheet has 8 lines, but the code outputs 25 (should output 9):

emptyRow = 1 + wb.Worksheets("TrackedItems").Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox emptyRow

实例，此答案将解决我的问题，因为电子表格不是稀疏填充（没有空白单元格与非空白单元格混合）。但是，我已经使用了 xlCellTypeLastCell ，并且没有遇到这个问题，包括在数据 sparseley填充的情况下。什么原因导致 xlCellTypeLastCell 以这种方式行事？有什么办法使其行为正常，还是依靠定制编码功能？我应该使用 xlCellTypeLastCell 替换我的旧作品与这些功能，或事实上，它目前的工作意味着它不会打破以后（至少如果我不更改任何

In this particular instance, this answer will solve my problem, because the spreadsheet is not sparsely populated (there are no blank cells mixed in with non-blank cells). However, I have used xlCellTypeLastCell many times, and not encountered this problem, including in situations where data is sparseley populated. What causes xlCellTypeLastCell to behave in this manner? Is there any way to make it behave properly, or must I rely on custom-coded functions? Should I replace my older work using xlCellTypeLastCell with such functions, or does the fact that it currently works mean that it won't break later (at least if I do not change any code in those workbooks)?

推荐答案

你可以尝试这样：

emptyRow = range("A" & activesheet.rows.count).end(xlup).row

可以返回最后一行的数据吗？

to return the last row with data in it maybe?

这篇关于xlCellTypeLastCell给出不正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！