VBA & Macro Problems

How to Automatically Create Excel Table Rows and Columns from Cell Values

Chanuka GeekiyanageChanuka Geekiyanage Nov 10, 2026 869 views

Question details

The user needs to automatically generate a structured table with specific rows and columns based on input values, such as creating a dynamic training plan from entered exercise names and set counts.

Automatically Create Excel Table Rows and Columns from Cell Values
Product
Excel
Device & OS
not provided
Scenario
Building a dynamic data entry form or training plan where the table structure automatically adjusts to match user-defined parameters.
Observed behavior
Standard worksheet functions return dynamic arrays but cannot automatically create or resize a structured Excel Table, requiring a programmed solution instead.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer options in your ribbon to access VBA features.

Solution 1Recommended

Use a VBA Macro to Create and Resize Tables

Create a custom VBA script to read the cell inputs and automatically generate a structured Excel Table.

While dynamic array functions can output data, they cannot create or resize a structured Excel Table. VBA allows you to read your input parameters (like rows, columns, and exercise names) to dynamically build the table.

Be careful when automating table creation, as resizing could overwrite or misalign existing data if placed incorrectly.

1
Enable the Developer Tab

Go to File > Options > Customize Ribbon and check the Developer option to make macro tools visible.

2
Open the VBA Editor

Click 'Visual Basic' on the Developer tab or press Alt + F11 on your keyboard.

3
Insert a New Module

Right-click on your workbook in the Project window, select Insert, and choose Module.

4
Write the Macro Code

Enter a script that defines your starting cell, reads the row and column counts from your input cells, uses 'ListObjects.Add' to create the table, and optionally clears the input form fields.

5
Assign the Macro to a Button

Insert a shape or button on your worksheet, right-click it, and select 'Assign Macro' to run your table-generation script with a single click.

Use a VBA Macro to Create and Resize Tables
Data Placement Warning: Always define what happens to existing data before resizing or adding a new table. Have your macro calculate the last used row to ensure previously entered values are not overwritten.
Powerful Spreadsheet Automation

Create Dynamic Tables with Macros in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly automate table creation, resize arrays, and build dynamic forms based on cell values.

  1. 1. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled (.xlsm) file.
  2. 2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon to access VBA and Macro features.
  3. 3. Open the VBA Editor: Click on the VBA Editor icon to write or paste your table-generation code.
  4. 4. Run your Macro: Execute the script to instantly create your customized rows and columns based on your cell values.
Full support for VBA (Visual Basic for Applications) macrosHighly compatible with Microsoft Excel (.xlsm) filesLightweight and fast spreadsheet processingFree and intuitive interface for data automation
microsoft office alternative - wps office

Frequently Asked Questions

Can I create a structured table from cell values without using VBA?

Standard worksheet functions can create dynamic spill arrays (using functions like SEQUENCE), but they cannot automatically generate or resize a formal structured Excel Table (ListObject). VBA or Office Scripts are strictly required to automate structured tables.

How do I prevent new tables from overwriting my existing data?

In your VBA script, you should dynamically calculate the last used row in your worksheet. Use this value to offset the new table's starting position so it is always placed below previously entered data.

Why does Excel restrict 'Format as Table' on dynamic arrays?

Excel restricts the 'Format as Table' feature over dynamic array formulas because dynamic arrays automatically resize based on their formula inputs, which directly conflicts with how Excel manages strict structured table bounds and references.