使用Excel VBA查找最后一行

Recently, I complained about having trouble getting Excel VBA to correctly find the last row with data in a column, when using Excel tables. Thanks for the suggestions on solving that problem!

最近，我抱怨使用Excel表时，无法使Excel VBA正确地找到列中包含数据的最后一行 。 感谢您提供解决该问题的建议！

Jim Cone was intrigued by the find last row challenge, and from previous Excel adventures, Jim knew that Excel tables aren't the only obstacle to finding the last row.

Jim Cone对查找最后一行的挑战很感兴趣，而且从以前的Excel冒险中，Jim知道Excel表不是查找最后一行的唯一障碍。

Jim decided to write a universal LastRow function for a...

Jim决定为...编写通用的LastRow函数。

1. Worksheet
   工作表
2. Range (selection)
   范围(选择)
3. Specific column – in a worksheet or range.
   特定列–工作表或范围中。
4. List/Table
   清单/表格
5. Filtered data
   过滤数据

For example, the LastRow function shows that row 40 has the last value in column E, even though that row is hidden by a filter.

例如，LastRow函数显示第40行在E列中具有最后一个值，即使该行被过滤器隐藏了也是如此。

如何找到最后一行 (How to Find the Last Row)

Covered with dust, and bits of Excel VBA code, Jim has finally emerged from his basement workshop. He sent me his Find Last Row sample workbook, which you can download at the link below.

吉姆布满了灰尘，并沾上了一些Excel VBA代码，终于从地下室车间出来了。 他给我寄了他的Find Last Row示例工作簿，您可以从下面的链接下载该工作簿。

Here's what Jim had to say about writing the code:

这是Jim在编写代码时不得不说的：

• • The find function is faster, works on List/Tables but fails on a filtered range and occasionally throws up an unexpected error.
    查找功能更快，可以在列表/表上使用，但是在过滤范围内失败，并且偶尔会引发意外错误。
  • The iteration method seems to be generally reliable but fails on both Lists/Tables and filtered ranges.
    迭代方法似乎通常是可靠的，但在列表/表和过滤范围上均失败。
• • Note that it is actually two functions (both are required).
    请注意，它实际上是两个功能(两者都是必需的)。
  • Use of the second function avoids a compile error in Excel versions prior to the 2003 version.
    使用第二个函数可以避免在2003版之前的Excel版本中出现编译错误。
  • The approach is to search using find and switch to iteration if an error occurs.
    方法是使用查找进行搜索，如果发生错误，则切换到迭代。

Thanks Jim!

谢谢吉姆！

下载样本工作簿 (Download the Sample Workbook)

To see Jim Cone's Excel VBA code, you can download the Find Last Row in a Column sample workbook. The file is in Excel 2003 format, and zipped. Enable macros when opening the file, if you want to test the code.

要查看Jim Cone的Excel VBA代码，您可以下载Column示例工作簿中的Find Last Row 。 该文件为Excel 2003格式，并已压缩。 如果要测试代码，请在打开文件时启用宏。

Jim has added a few Excel tables to the worksheet, which you'll see in Excel 2007 and Excel 2010. In Excel 2003, those will appear as lists.

Jim在工作表中添加了一些Excel表格，您将在Excel 2007和Excel 2010中看到这些表格。在Excel 2003中，这些表格将显示为列表。

Click the button on the worksheet, to find the last row with data in the current selection. Or, you can modify the "DoesItWork" sub, so it will find the last row on a worksheet or specific column. ________________

单击工作表上的按钮，以找到当前所选内容中的最后一行数据。 或者，您可以修改“ DoesItWork”子项，以便它将在工作表或特定列上找到最后一行。 ________________