VBA & Macro Problems

How to Add Multiple Table Rows from an Excel Form Entry Using VBA

Elise Williams Oct 10, 2026 869 views

Question details

The user wants an automated way (like an Excel form or macro) to input a specific number, such as 25, and instantly add that exact number of rows to the bottom of a designated table named GODSOY on Sheet1.

How to Add Multiple Table Rows from an Excel Form Entry Using VBA

Product: Excel
Device & OS: not provided
Scenario: Expanding a formatted dataset dynamically without having to manually insert rows one by one or drag down the table handle.
Observed behavior: Native Excel requires manual row insertion, so the user needs a custom VBA macro or user form script to accept a numeric input and loop the row addition process.

Before you start

Ensure you have the Developer tab enabled in your Excel ribbon and remember to save your workbook as an Excel Macro-Enabled Workbook (.xlsm) so your VBA code is not lost upon closing.

Solution 1Recommended

Use an InputBox Macro to Add Rows to a Specific Table

This is the most efficient solution. It prompts the user for a number and uses a loop to add that exact amount of rows to the bottom of the named table.

By utilizing the ListObjects property in VBA, you can target specific tables regardless of where they are on the worksheet. The InputBox acts as a simple 'form entry' to capture the number of rows required.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window. In the top menu, click 'Insert' and select 'Module'.

Paste the VBA Code

Copy and paste the following code into the blank module window: Sub AddRowsToTable() Dim tbl As ListObject Dim numRows As Variant Dim i As Integer numRows = InputBox("Enter the number of rows to add to the table:", "Add Multiple Rows") If numRows = "" Or Not IsNumeric(numRows) Then Exit Sub ' Change Sheet1 and GODSOY to match your actual sheet and table name Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("GODSOY") For i = 1 To CInt(numRows) tbl.ListRows.Add Next i End Sub

Assign Macro to a Button

Close the VBA editor and return to Excel. Go to the Developer tab, click 'Insert', and choose 'Button (Form Control)'. Draw the button on your worksheet, and when prompted, assign the 'AddRowsToTable' macro to it.

Test the Input Form

Click the newly created button. Type a number (e.g., 25) into the prompt box and click OK. The table will instantly expand by that many rows at the bottom.

Use an InputBox Macro to Add Rows to a Specific Table

Table References: Because the macro uses 'ListRows.Add', any formulas or formatting present in your existing table will automatically copy down to the newly created rows.

Solution 2

Create a Custom VBA UserForm for Row Entry

If you require a more professional, standalone dialog box instead of a simple prompt, creating a UserForm provides a dedicated graphical interface.

Advanced Spreadsheet Automation

Automate Your Tables Smoothly with WPS Spreadsheet

WPS Office Spreadsheet provides full support for VBA macros, allowing you to run custom row-adding scripts and manage complex data tables just as you would in Microsoft Excel.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm workbook containing the data table.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon. If it is hidden, you can enable it in the Options menu under Customize Ribbon.
3. Launch the VBA Editor: Click on the 'VBA Editor' icon to open the programming environment, where you can paste or edit your row-addition macro.
4. Execute the script: Run the macro using a form control button on your sheet to instantly expand your table as needed.

Fully compatible with Microsoft Excel VBA macros and .xlsm file formats.Built-in Developer tab for quick access to the VBA Editor and form controls.Lightweight architecture ensures fast script execution even on large tables.A free, comprehensive alternative for daily data processing and office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Subscript out of range' error when running the macro?

This error occurs if the macro cannot find the specified sheet name or table name. Double-check that your worksheet is named exactly 'Sheet1' and your table is named 'GODSOY' (Check via Table Design > Table Name).

Will the new rows copy the formulas from the existing table rows?

Yes. When you use the 'ListRows.Add' method in a formatted Excel table (ListObject), Excel automatically extends the table formatting, data validation, and calculated column formulas into the new rows.

Can I modify the macro to insert rows at the top of the table instead of the bottom?

Yes, you can specify the position of the new row. Change 'tbl.ListRows.Add' to 'tbl.ListRows.Add(Position:=1)' to insert the rows at the very top of the table data body.

How do I ensure users only enter valid numbers in the InputBox?

You can wrap the InputBox result in a validation check. Use the 'IsNumeric()' function in VBA to verify the input is a number. If it is not, use a MsgBox to prompt the user to try again.

Excel & Data Problems