VBA & Macro Problems

How to Insert Multiple Rows Above Matching Cells Across Excel Worksheets using VBA

Jason Miller Oct 15, 2026 869 views

Question details

The user needs a method to automate the insertion of a specified number of blank rows immediately above every cell containing a specific text string (such as 'Total') across multiple worksheets that share the same structural layout.

How to Insert Multiple Rows Above Matching Cells Across Excel Worksheets using VBA

Product: Excel
Device & OS: not provided
Scenario: Organizing and expanding summary tables across numerous tabs where adding multiple blank rows above existing total lines manually is inefficient and time-consuming.
Observed behavior: Currently, inserting rows manually takes too much time. The goal is to deploy a VBA macro that prompts the user for the number of rows to add and applies this across all identical worksheets automatically.

Before you start

Before running any VBA macros, always save a backup copy of your workbook. Actions performed by VBA scripts generally bypass Excel's standard Undo stack, meaning the changes cannot be easily reversed.

Solution 1Recommended

Use an InputBox VBA Macro to Insert Rows

Implement a VBA macro that loops through each worksheet, searches for your target string, and uses an InputBox prompt to dynamically determine how many rows to insert above the match.

This solution utilizes a macro that steps backwards through your rows to prevent row shifting from skipping matches. By integrating an InputBox, the script becomes flexible, allowing you to insert one, two, or any specified number of blank rows on the fly.

Open the VBA Editor

Open your target Excel workbook and press ALT + F11 to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click on 'Insert' in the top menu bar and select 'Module' to open a new blank coding window.

Write the Macro Code

Paste your customized VBA code into the module. Ensure the script includes a 'For Each' loop to iterate through worksheets, a 'Find' method for your target text (e.g., 'Total'), and an 'InputBox' variable to collect the desired number of rows.

Define the Search Range

Within your script, modify the column reference to match where your target text is located (for example, change the range reference to Columns("A") if 'Total' is in column A).

Run the Macro

Press F5 or click the green 'Run' button. When the prompt appears on your screen, type the number of rows you want to insert and click OK. The script will automatically add the rows across all specified worksheets.

Use an InputBox VBA Macro to Insert Rows

Code Flexibility: You can easily reuse this macro for different keywords by simply changing the search string in the code from 'Total' to any other desired text.

Automate Data Processing

Automate Excel Tasks Easily with WPS Spreadsheet

WPS Office offers robust support for VBA macros, allowing you to run your row-insertion scripts and automate repetitive worksheet formatting tasks effortlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsx workbook that requires row formatting.
2. Access the VBA Editor: Navigate to the 'Developer' tab in the top ribbon and click on 'VBA Editor', or simply press ALT + F11.
3. Run Your Macro: Insert a new module, paste your row insertion VBA code, and hit Run to process all your worksheets instantly.

Fully supports running and editing standard VBA macro codes.Highly compatible with Microsoft Excel (.xlsx and .xlsm) formats.Lightweight, fast performance for heavy datasets and multiple worksheets.Free to download with a familiar, easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the target word from 'Total' to something else?

Yes, you can easily modify the macro code. Locate the line containing the search string parameter (e.g., LookAt:="Total") and replace 'Total' with your desired text. Keep the quotation marks around your new text.

How do I make the macro insert rows below the cell instead of above?

To insert rows below the target cell, modify your VBA script's row offset logic. Change the insertion command from applying to the current row (e.g., cell.EntireRow.Insert) to applying to the row directly underneath (e.g., cell.Offset(1, 0).EntireRow.Insert).

Will this macro affect hidden worksheets in my workbook?

By default, a macro that iterates using 'For Each ws In ActiveWorkbook.Worksheets' will attempt to execute on hidden sheets as well. If you want to skip hidden sheets, add an 'If ws.Visible = xlSheetVisible Then' conditional statement at the beginning of your loop.

Excel & Data Problems