在表下方添加数据时，Excel VBA扩展表 [英] Excel VBA extend table when data added below table

问题描述

我在Excel中有一个表，当用户在表后添加数据时，该数据不属于该表.

I have a table in Excel that when users add data after the table, the data doesn't belong to the table.

我创建了一个可以执行以扩展表的代码.代码如下:

I've created a code that can be executed to extend the table. The code is as follows:

Sub ExtendTableToLastRow()

    Sheets("Update").Select
    If ActiveSheet.FilterMode Then ActiveSheet.AutoFilter.ShowAllData
    'LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = ActiveSheet.Range("a1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row

    ActiveSheet.ListObjects("TUpdate").Resize Range("$A$2:$AK$" & LastRow)
    Range("A1").Select
End Sub

问题是这必须手动执行.

The problem is this has to be executed manually.

根据评论，我创建了以下代码:

Based on the comments I've created the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Sizes the table to include all new rows
    Application.EnableEvents = False
        If ActiveSheet.FilterMode Then ActiveSheet.AutoFilter.ShowAllData
        LastRow = ActiveSheet.Range("a1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
        ActiveSheet.ListObjects("TUpdate").Resize Range("$A$2:$AK$" & LastRow)
    Application.EnableEvents = True
End Sub

但是，当我在表格下的单元格中添加一个值，但是现在我复制一行时，代码就会执行.

But the code executes when I add a value in a cell under the table, but now when I copy a row.

推荐答案

您可以将代码作为Worksheet_Change事件的处理程序.

You can put your code as a handler for the Worksheet_Change event.

MSDN上的存档

这篇关于在表下方添加数据时，Excel VBA扩展表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！