Excel表的最后一行不正确

最新推荐文章于 2024-08-03 16:10:46 发布

culiao2169

最新推荐文章于 2024-08-03 16:10:46 发布

阅读量421

点赞数

文章标签： python java 人工智能机器学习 mysql

原文链接：https://contexturesblog.com/archives/2011/06/24/last-row-incorrect-with-excel-table/

版权

When you're working with Excel VBA, you might want find the last row with data, so you can paste new data in the row below that.

使用Excel VBA时，您可能希望找到包含数据的最后一行，因此可以将新数据粘贴到该行下面的行中。

The following code works up from the last row on the worksheet, until it hits a cell with data. It's like using the End key and Up arrow, to manually move from the bottom of the worksheet.

下面的代码从工作表的最后一行开始工作，直到命中包含数据的单元格为止。就像使用“结束”键和向上箭头，从工作表的底部手动移动一样。

Sub GetLastRow()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lRow
End Sub

In the sample sheet, the last data in column A is in row 10, and that's the result when running the code.

在样本表中，A列中的最后一个数据在第10行中，这就是运行代码时的结果。

意外结果 (Unexpected Result)

This week, I was using similar code in a client's workbook, to find the last row of data. The data was in a named Excel table, and the last couple of rows in the table were blank. I wanted to find the last row with data, so I could change the table definition, to end at that last row.

本周，我在客户工作簿中使用类似的代码来查找数据的最后一行。数据位于一个命名的Excel表中，表中的最后两行为空白。我想找到包含数据的最后一行，因此可以更改表定义，以在最后一行结束。

When I used the same Excel VBA code, the last row wasn't what I expected – it found the last row in the named table, even though that row was empty.

当我使用相同的Excel VBA代码时，最后一行不是我期望的-它在命名表中找到了最后一行，即使该行为空。

I'm sure that information is helpful in some situations, but it sure wasn't going to help me resize the table!

我确定这些信息在某些情况下会有所帮助，但肯定不会帮助我调整桌子的尺寸！

我的最后一行解决方法 (My Last Row Workaround)

There might be a more sophisticated solution to this problem, but I added a line of code to resize the table, so it ends at row 2.

可能有一个更复杂的解决方案，但是我添加了一行代码来调整表的大小，因此它在第2行结束。

Then, the code found the correct last row of data, and resize the table to end at that row.

然后，代码找到正确的最后一行数据，并调整表的大小以在该行结束。

Sub ResizeTheTable()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ActiveSheet
ws.ListObjects(1).Resize ws.Range("$A$1:$H$2")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.ListObjects(1).Resize ws.Range("$A$1:$H$" & lRow)
MsgBox lRow
End Sub

That fixed the problem, and the table resized correctly.

这样可以解决问题，并且表的大小可以正确调整。