VBA & Macro Problems

Define a Dynamic Excel Table Range With VBA

Huma Ashraf ChHuma Ashraf Ch Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

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.

2
Add the dynamic range code

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"

3
Adjust and run the macro

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.

Build the Excel Table Range From the Last Used Row
Check the last used cell: SpecialCells(xlLastCell) uses Excel's last used cell, which can extend beyond the data because of formatting or previously used cells. If every data row has a value in column A, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row instead.
Free Microsoft Office alternative

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.

Work with spreadsheet data and table ranges in a familiar interfaceOpen and edit Microsoft Excel XLSX and XLS filesUse a free, lightweight alternative for everyday Office tasksMove existing spreadsheets into WPS Office without starting from scratch
microsoft office alternative - wps office

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.