VBA & Macro Problems

How to Insert Rows Above and Copy Data with Excel VBA

Amos Gikunda Sep 25, 2026 869 views

Question details

The user needs to write an Excel VBA macro that inserts new rows above the active row and populates them by copying and dynamically combining data from specific columns.

How to Insert Rows Above and Copy Data with Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating spreadsheet formatting where new rows must be inserted dynamically and populated with a combination of static text and existing cell values from other columns.
Observed behavior: The user needs the correct VBA syntax to execute row insertion and accurately assign concatenated text and formulas without breaking row references.

Before you start

Before running any VBA macros, ensure you have saved a backup copy of your workbook, as macro execution cannot be undone using the standard Undo feature.

Solution 1Recommended

Use EntireRow.Insert and Formula Assignment in VBA

Insert rows using the active cell's row index and populate the new cells by concatenating values from specific columns using VBA formula assignment.

By utilizing the EntireRow.Insert method, you can dynamically push existing data down and create space above the active row. You can then use string concatenation in VBA to build formulas that pull and combine data from specific columns like D and K.

Identify the target row index

Determine the row number of your target reference by declaring a variable in your VBA script, such as `r = ActiveCell.Row`.

Insert the new row

Use the insert method to add a new row directly above your specified row by writing `Rows(r).EntireRow.Insert` in your code.

Assign dynamic formulas

Assign a formula to the newly created row to copy and combine data. For example, to combine the text 'PAL' with columns D and K, use: `Range("G" & r).Formula = "=\"PAL \"&D" & r & "&\"-\"&K" & r`.

Apply text replacements (Optional)

If you need to clean up or replace specific strings after combining the data, apply the VBA `Replace` function to the target range's values.

Use EntireRow.Insert and Formula Assignment in VBA

Dynamic Row Tracking: When inserting multiple rows using a loop in VBA, always step backwards (e.g., `For i = LastRow To 2 Step -1`) to prevent row reference shifts from affecting your macro.

Advanced Spreadsheet Automation

Automate Tasks with WPS Spreadsheets Macros

WPS Spreadsheets provides robust support for macros and VBA scripts, allowing you to seamlessly insert rows, copy data, and automate repetitive formatting tasks with high compatibility.

1. Enable Developer Tools: Open your workbook in WPS Spreadsheets and navigate to the 'Developer' tab on the ribbon to access Macro features.
2. Open the VBA Editor: Click on the 'Visual Basic' icon or press Alt + F11 on your keyboard to launch the integrated script editor.
3. Insert and Run Code: Go to Insert > Module, paste your row-insertion and data-copying VBA code into the window, and click 'Run' to execute the automation.

Highly compatible with Microsoft Excel (.xlsx and .xlsm) formats and macro scripts.Lightweight software with fast execution for complex data manipulation and row insertion.Built-in developer tools for editing, debugging, and running code efficiently.Free and user-friendly interface for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

How do I insert multiple rows at once using VBA?

You can specify the range size before calling the insert method. For example, using `Rows("5:7").EntireRow.Insert` will insert three blank rows above row 5 simultaneously.

Why do my row references point to the wrong data after inserting a row in a loop?

Inserting a row shifts all subsequent rows down by one. If your macro loops from top-to-bottom, the references will quickly become misaligned. Always loop bottom-to-top (`Step -1`) when inserting or deleting rows in VBA.

Can I copy the formatting of the row above when inserting a new row?

Yes, you can copy the source row using `Rows(r).Copy` and then use `Rows(r + 1).Insert Shift:=xlDown` to duplicate the row along with its exact formats and formulas.

How do I include literal quotation marks in a VBA formula string?

To include double quotes inside a VBA string, you must double them up. For example, to write `="Text" & A1` into a cell, your VBA code should look like `Range("B1").Formula = "=""Text"" & A1"`.

Excel & Data Problems