VBA & Macro Problems

How to Insert Rows and Copy Formulas Using Excel VBA

Huda Qurayshi Oct 9, 2026 869 views

Question details

The user needs a VBA macro to insert a specific number of rows before a selected row and automatically copy formulas from the preceding row into the newly inserted rows, specifically targeting columns D through M.

How to Insert Rows and Copy Formulas Using Excel VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating row insertion and formula duplication using an Excel VBA script to improve data entry efficiency.
Observed behavior: Formulas in columns D through M need to be programmatically copied down to the new rows based on validated user input for row count and insertion location.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab in your ribbon to access the VBA editor.

Solution 1Recommended

Use VBA InputBox and FillDown Method to Insert Rows and Copy Formulas

Create a macro utilizing Application.InputBox to prompt for row insertion details, followed by the FillDown method to accurately duplicate the formulas across specific columns.

By leveraging the Application.InputBox method, you can capture and validate user input to ensure that row counts and target locations are greater than zero. Once the new rows are added, the FillDown method efficiently stretches the formulas from the preceding row into the newly created blank rows without copying hardcoded data.

Open the VBA Editor

Press Alt + F11 on your keyboard while in Excel to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click Insert and select Module to create a blank workspace for your macro script.

Set Up the Input Prompts

Write a subroutine and use Application.InputBox to collect 'iRow' (the row number to insert before) and 'iCount' (the number of rows to insert). Ensure you add conditions to check that both inputs are greater than zero.

Add the Row Insertion Command

Use the command Rows(iRow).Resize(iCount).Insert to instruct Excel to insert the requested number of rows at the specified location.

Apply the FillDown Method

Add the code Range("D" & iRow - 1).Resize(iCount + 1, 10).FillDown. This targets column D through M (a span of 10 columns) from the row directly above the insertion and copies its formulas downwards.

Run Your Macro

Save your code, close the editor, and run the macro by pressing Alt + F8 in Excel, selecting your script, and clicking Run.

Use VBA InputBox and FillDown Method to Insert Rows and Copy Formulas

Understanding the Resize Function: In the FillDown command, the number '10' in Resize(iCount + 1, 10) corresponds to the 10 columns from D to M. Adjust this width argument if your formula range changes.

Seamless Macro Automation

Use WPS Spreadsheet to Easily Run VBA Macros and Manage Data

WPS Spreadsheet fully supports VBA macros, allowing you to run powerful automation scripts like row insertions and formula duplication just as easily as you would in Microsoft Excel.

1. Download and Install WPS Office: Get WPS Office from the official website and open your .xlsm file using WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon to access macro recording and editing tools.
3. Open the VBA Editor: Click the 'Visual Basic Editor' button or press Alt + F11 to view your row-inserting macro code.
4. Execute the Macro: Run the script directly from the editor or assign it to a worksheet button to automate your formula duplication instantly.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) workbook formats.Robust built-in VBA editor to write, edit, and troubleshoot macro scripts.Lightweight application with a familiar interface for immediate productivity.Free to download and use for your advanced spreadsheet automation needs.

microsoft office alternative - wps office

Frequently Asked Questions

How do I modify the VBA code if my formulas are in columns A to C?

You need to update the Range reference in your VBA script. Change it to Range("A" & iRow - 1).Resize(iCount + 1, 3).FillDown. The '3' indicates the span of three columns (A, B, and C).

Why is my macro failing to run when I press Alt + F8?

Your macro security settings might be blocking execution, or the workbook is not saved in a macro-enabled format (.xlsm). Go to the Trust Center settings to enable macros for the current session.

Can I undo the row insertion performed by the VBA macro?

Standard undo functionality (Ctrl + Z) generally does not work for actions executed by VBA macros. It is highly recommended to save a backup copy of your workbook before testing or running new scripts.

Excel & Data Problems