Define a Dynamic Excel Table Range With VBA
Question details
Create an Excel table from a VBA range that adjusts to the number of populated rows.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro inserts data and needs to create a table without hard-coding a range such as A1:BV55.
- Observed behavior
- The desired table range starts at A1, ends at the required final column, and extends to the last row of data.
Insert the data before determining the last row, and confirm which column should be the table's final column. The example assumes row 1 contains headers and the worksheet does not already have a table named Table1.
Build the Excel Table Range From the Last Used Row
Use Excel VBA to find the last used row and create a table whose height changes with the worksheet data.
In Excel, open the Developer tab and select Visual Basic. In the VBA editor, select Insert > Module or open the module containing your existing macro.
After the data-insertion code, add: Dim ws As Worksheet Dim lastRow As Long Set ws = ActiveSheet lastRow = ws.Cells.SpecialCells(xlLastCell).Row ws.ListObjects.Add(xlSrcRange, ws.Range("A1:BV" & lastRow), , xlYes).Name = "Table1"
Replace BV with the required final column, such as BY, and change Table1 if needed. Run the macro from Developer > Macros, then check that the new table covers the intended rows and columns.

Work With Excel-Compatible Spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move everyday spreadsheet work. Excel VBA behavior can differ, so test existing macros before relying on them in WPS.

Frequently Asked Questions
Why does the table include extra blank rows?
Excel's SpecialCells(xlLastCell) can include cells affected by formatting or earlier content. Check the worksheet's last used cell, or use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row if column A is populated for every data row.
How do I change the table's last column?
Change BV in ws.Range("A1:BV" & lastRow) to the required column letter. For example, use ws.Range("A1:BY" & lastRow) to end the table at column BY.
What does xlYes mean in the table creation code?
In ListObjects.Add, xlYes tells Excel that the first row of the selected range contains headers. Use the appropriate header setting if row 1 contains data instead.
Why might the macro fail when I run it a second time?
The code creates a new table named Table1. If that name is already in use or the range overlaps an existing table, modify the existing table or remove it before creating another one.




