Excel VBA extend table when data added below table

Question

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.

Answer 1

You can change Excel setting to do that :

File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and check Include new rows and columns in table

Or run this line only one time:

Application.AutoCorrect.AutoExpandListRange = True

Answer 2

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

Arrtice on MSDN

Answer 3

Hey I believe this should help!

good luck .

http://www.tushar-mehta.com/publish_train/xl_vba_cases/0101%20Create%20a%20self%20adjusting%20range.htm