VBA & Macro Problems

Add Data to the Next Available Excel Table Row with VBA

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

Find an available row within an Excel table and enter data using VBA without converting the table to a normal range.

Add Data to the Next Available Excel Table Row with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro needs to enter data into an existing Excel table that may have other worksheet data below it.
Observed behavior
A worksheet-based End(xlUp) calculation can select a row below the table when data exists farther down the worksheet.
Before you start

Click inside the table and check its name on the Table Design tab, then confirm the worksheet name. Open the VBA editor with Alt+F11 and ensure the workbook is saved in a macro-enabled format such as .xlsm.

Solution 1Recommended

Fill the first empty table row, or add one if needed

Use this approach when an existing blank row inside the table should be filled before the table is expanded.

The macro checks rows belonging to the table rather than scanning the worksheet. It treats a row as available only when all its table cells are empty; cells containing formulas are not considered empty.

1
Open a VBA module

In Excel, press Alt+F11, select your workbook in the Project pane, and choose Insert > Module.

2
Add the table-row macro

Paste this code into the module: Sub FillNextTableRow() Dim tbl As ListObject Dim r As ListRow Dim targetRow As ListRow Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1") For Each r In tbl.ListRows If Application.WorksheetFunction.CountA(r.Range) = 0 Then Set targetRow = r Exit For End If Next r If targetRow Is Nothing Then Set targetRow = tbl.ListRows.Add(AlwaysInsert:=True) End If targetRow.Range.Cells(1, 1).Value = "Example value" End Sub

3
Customize and run the macro

Replace Sheet1 and Table1 with your worksheet and table names. Replace Example value with your data, then run FillNextTableRow from the VBA editor or Excel's Macros dialog.

Fill the first empty table row, or add one if needed
Adding a row can move worksheet cells: If no completely empty table row exists, ListRows.Add expands the table. With AlwaysInsert set to True, cells below the insertion point may be shifted down.
Free Microsoft Office alternative

Edit Excel spreadsheets with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move existing files into WPS. Excel VBA compatibility can vary, so check your macros before relying on them in WPS.

Edit spreadsheet data in a familiar interfaceOpen and save common Microsoft Excel formats, including XLSXWork with table data without relying on worksheet-wide row searchesUse a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why does End(xlUp) find a row below my table?

End(xlUp) searches worksheet cells, not the table boundary. If a cell farther down the column contains data, the result can be below the table. Use the table's ListObject and ListRows collection instead.

Does ListRows.Add fill a blank row already in the table?

No. ListRows.Add creates a new row at the bottom of the table. To reuse an existing blank row, loop through tbl.ListRows and check each row before adding one.

How do I find my Excel table name?

Click any cell in the table and open the Table Design tab. Use the name shown in the Table Name box in your ListObjects reference.

Can I write values to other columns in the selected row?

Yes. Use targetRow.Range.Cells(1, 2).Value for the second table column, or change the column number to match the field you want to populate.

Will the blank-row check treat a formula returning an empty string as empty?

No. The example uses COUNTA, which counts cells containing formulas even if they display an empty string. Adjust the availability test if your table uses formulas in otherwise available rows.