[Excel VBA] How to Update (Resize) a Table Range | Flexibly Handle Data Changes

Once you create a table (ListObject) in Excel, you may need to dynamically update its target range as data is added or removed.

By using the VBA .Resize method, you can smartly reconfigure the range without deleting and recreating the table.

In this article, I will introduce how to implement VBA code to dynamically change the cell range handled by a table.

Expected Use Cases

• You want to adjust the table range to match changes in the number of rows or columns.
• You want to always include data up to the last row of the sheet.
• You want to automate table updates while maintaining filters and formatting.

VBA Code Examples

Method 1: Update to a Fixed Range

Sub ResizeTableFixedRange()

    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("ProductList")

    ' Change the table scope to a fixed cell range
    tbl.Resize Range("B2:D10")

End Sub

Method 2: Get Last Row and Update Dynamically

Sub ResizeTableDynamic()

    Dim tbl As ListObject
    Dim lastRow As Long
    Dim newRange As Range

    Set tbl = ActiveSheet.ListObjects("ProductList")

    ' Get the last row of column C (excluding blanks)
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row

    ' Define the new table range (Must include the header row)
    Set newRange = Range("B2:D" & lastRow)

    ' Update the table range
    tbl.Resize newRange

End Sub

Specifications and Notes on the Resize Method

Advanced Example: When Columns Change Dynamically

If the number of columns is also variable, you can handle it as follows:

Dim lastCol As Long
' Get the last column based on row 2
lastCol = Cells(2, Columns.Count).End(xlToLeft).Column

' Set range from B2 to the last row and last column
Set newRange = Range(Cells(2, 2), Cells(lastRow, lastCol))

Summary

By utilizing the Excel VBA Resize method, you can update just the data range without breaking the existing table structure. This allows you to preserve headers, styles, and filter settings.

This is a highly useful technique for anyone wanting to build automated update processes for data that fluctuates daily. Please try introducing this method to enhance your business templates and shared documents.