How to Automatically Create Rows Based on Input Counts in WPS Spreadsheets
Question details
The user needs to automatically produce a variable number of property sections or rows in a spreadsheet based on counts entered on an instruction sheet, and must choose between display-only dynamic arrays, a structured table, or VBA automation.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains an instruction sheet with counts; the user wants matching rows or property sections to appear on other sheets without manually inserting or deleting them.
- Observed behavior
- The current layout is static, so changing the input counts does not automatically add or remove the required rows.
Decide whether the generated rows are display-only or will receive additional data entry, and define what should happen when the input count increases or decreases. This determines whether formulas, a table, or a VBA macro is the right approach.
Generate display-only rows with dynamic array formulas
Use this method when users only need to see the generated rows and will not type unique data into each spilled cell.
Dynamic array formulas such as SEQUENCE, FILTER, and VSTACK update immediately when the instruction-sheet count changes, so they are the simplest no-code option for display-only sections.
In WPS Spreadsheets, open your workbook and type the required number of rows into a dedicated count cell on the instruction sheet, such as B2.
On the sheet where rows should appear, select the first output cell and enter =SEQUENCE(Instruction!$B$2,1,1,1) to generate one numbered row per count.
If you need to stack a header or existing records, use =VSTACK(header_range, SEQUENCE(Instruction!$B$2,1,1,1)) or =FILTER(data_range, condition) to display only the required rows.
Press Enter, then make sure the spill area has enough blank cells. WPS Spreadsheets automatically fills the output range.
Insert editable rows with a VBA macro
Use this method when users must enter data into each generated row and those rows must remain editable records.
Prepare a structured Excel Table for manual expansion
Use this method when you prefer a manual, low-code structure and want rows to be added contextually as users type.
Generate the Exact Rows You Need in WPS Spreadsheets
You can create variable row counts in WPS Spreadsheets using SEQUENCE, FILTER, or VSTACK for display-only output and VBA for editable rows. WPS Office is a free, lightweight Office suite that opens and saves Excel .xlsx and .xlsm files with a familiar interface.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the instruction sheet, and ensure the target sheets are ready.
- 2. Enter the required count: Type the number of rows or property sections you need into a dedicated cell, such as B2 on the instruction sheet.
- 3. Apply a formula or VBA macro: For display-only rows, use =SEQUENCE(Instruction!$B$2,1,1,1) or =VSTACK(...). For editable rows, press Alt+F11 and insert a macro that inserts rows based on the count.
- 4. Test the result: Change the input count and confirm that the output rows update or that the macro creates the expected number of editable rows.
Frequently Asked Questions
Can I use SEQUENCE and VSTACK in WPS Spreadsheets?
Yes. In supported versions of WPS Spreadsheets, enter a dynamic array formula such as =SEQUENCE(Instruction!$B$2,1,1,1) and press Enter. The spill range fills automatically when enough blank cells are available.
How do I make the generated rows editable?
Dynamic array formulas create display-only spill cells. To make generated rows editable, use a VBA macro to insert actual worksheet rows, or use a structured Excel Table and enter data manually below the last table row.
Will an Excel VBA macro work in WPS Office?
WPS Office supports VBA macros in many .xlsm workbooks. Press Alt+F11 to open the editor, Alt+F8 to run a macro, and enable macros if the security prompt appears.
What happens when the input count decreases?
Dynamic array formulas automatically reduce the spill range. If you use VBA, the macro must include a step to delete extra rows or you must run a separate cleanup routine, so define that behavior before designing the workbook.
Do I need Microsoft Excel to create count-based rows?
No. WPS Spreadsheets can open and save .xlsx and .xlsm files, and you can create count-based rows with formulas or VBA without installing Microsoft Excel.
