隐藏最后一行时查找Excel电子表格的最后一行 [英] Finding the last row of an Excel spreadsheet when the last row is hidden

问题描述

我正在尝试在A列中找到包含具有以下代码的值的最后一行:

I'm trying to find the last row in column A that contains a value with the following code:

LastRow = DataWorksheet.Range("A:A").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

这在大多数情况下都可以正常工作， 除外，当最后几行被过滤掉时.例如，假设我们有30行数据.如果1-10行可见，11-20被滤除，21-30可见，则成功找到最后一行:返回30.当所有内容都可见并且21-30行被滤除时，LastRow返回1.

This works fine for most cases, except when the last few rows are filtered out. For instance, let's say we have 30 rows of data. If rows 1-10 are visible, 11-20 are filtered out, and 21-30 are visible, it finds the last row successfully: it returns 30. When everything is visible and rows 21-30 are filtered out, LastRow returns 1.

请注意，如果我手动隐藏而不是过滤掉第21-30行，它会告诉我最后一行是20.

Note that if I manually hide instead of filtering out rows 21-30, it tells me that the last row is 20.

有什么作用?如果最后几行被过滤，如何确定最后一行是什么?

What gives? How can I make it determine what the last row is if the last rows are filtered?

现在，LastRow似乎正在选择最后一个未过滤的行，这与以前的行为有一定的出入.一旦我能够更好地隔离遇到的错误/不一致，我将更新此帖子.

Now it seems as though LastRow is picking out the last unfiltered row, which is a definite departure from its previous behavior. I'll update this post once I'm better able to isolate the bug/inconsistency I'm encountering.

推荐答案

这些应忽略过滤/可见性，并为您提供最后使用的行号:

These should ignore filtering / visibility and give you the last used row number:

DataWorksheet.UsedRange.Rows.Count

-或-

DataWorksheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row

在A列中都找不到最后使用的单元格，但是...这就是您所需要的吗?

Neither will find the last used cell in column A, however... is that what you need?

这篇关于隐藏最后一行时查找Excel电子表格的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！