VBA & Macro Problems

How to Insert Multiple Formatted Rows Using Excel VBA

John Wilson Oct 19, 2026 869 views

Question details

The user wants to modify an Excel VBA button to insert multiple rows (e.g., five or ten) at once or prompt for a user-defined number, while ensuring the inserted rows retain the formatting and formulas of the selected row.

How to Insert Multiple Formatted Rows with an Excel VBA Button

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data entry in a spreadsheet by using a macro button to duplicate row structures, formulas, and formatting without inserting blank cells.
Observed behavior: Standard insertion creates blank rows, requiring the user to manually copy formulas and formats from previous rows, which is inefficient and repetitive.

Before you start

Before running any macros, ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and back up your data, as VBA actions cannot be reversed using the standard Undo feature.

Solution 1Recommended

Use VBA to Insert a Fixed Number of Formatted Rows

Use this VBA script to automatically copy the currently selected row and insert a fixed number of rows (like 5 or 10) directly below it, complete with formulas and formatting.

This method avoids creating empty cells by directly copying the active row and using PasteSpecial to paste only the formulas and formats into the newly inserted destination range.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor. Click 'Insert' from the top menu and select 'Module'.

Add the row-copying code

Inside the module, start your macro and capture the selected row by typing: lRow = Selection.Row. Next, copy the row using: Rows(lRow).Copy.

Insert the multiple rows

To insert five rows below your selection, add this line: Rows(lRow + 1 & ":" & lRow + 5).Insert Shift:=xlDown. (If you want 10 rows, change the 5 to a 10).

Paste formulas and formatting

Finally, apply the formulas and structure to the new rows by adding: Rows(lRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone. Save the module.

Use VBA to Insert a Fixed Number of Formatted Rows

Test Your Code: Assign this macro to a Form Control Button on your worksheet to execute the multi-row insertion with a single click.

Solution 2

Prompt the User to Define the Number of Rows

Modify the VBA script to display an input box, allowing the user to dynamically enter how many formatted rows they want to add at the moment of clicking the button.

Automate Row Insertion with WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros and advanced developer tools, allowing you to run custom scripts effortlessly to automate your repetitive spreadsheet tasks.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Macro-Enabled Workbook (.xlsm).
2. Access Developer Tools: Navigate to the Developer tab located on the top ribbon menu to access macro functionalities.
3. Open the VB Editor: Click on 'VB Editor' to open the script environment, where you can paste or modify your row-insertion VBA code.
4. Assign to a button: Use the 'Insert' option within the Developer tab to add a Form Control Button to your sheet, and assign your new macro to it.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formatsRobust macro execution for complex data automationLightweight, fast, and highly intuitive user interfaceFree basic features with powerful developer options available

microsoft office alternative - wps office

Frequently Asked Questions

How do I assign my VBA macro to a button on the worksheet?

Go to the Developer tab, click 'Insert', and select the 'Button (Form Control)' icon. Draw the button on your worksheet. An 'Assign Macro' dialog box will immediately appear; select your newly created row-inserting macro from the list and click OK.

Will this VBA code copy data validation like drop-down lists?

Yes. Because the code copies the entire selected row and pastes both formatting and formulas into the newly inserted rows, any data validation rules (like drop-down lists) applied to the original row will be perfectly duplicated.

How do I insert rows above the selected row instead of below?

To insert rows above your selection, you need to adjust the range math in the VBA code. Instead of copying to `Rows(lRow + 1 & ":" & lRow + 5)`, you would target `Rows(lRow - 5 & ":" & lRow - 1)`. Be careful to ensure you are not on row 1 to avoid a runtime error.

Why does the macro copy my typed text instead of just formulas?

If you are using a basic Copy and Insert command, static values (text and numbers) will be copied along with formulas. To keep the cells blank while retaining formulas, add a line of code after pasting to clear constants: `Selection.SpecialCells(xlCellTypeConstants).ClearContents`.

Excel & Data Problems