VBA & Macro Problems

How to Insert Excel Rows Based on a Cell Value with VBA

Maira Mehtab Sep 21, 2026 871 views

Question details

The user needs to insert a specific number of rows at row 35 automatically, calculated by subtracting 10 from the value in cell H9, provided the result is greater than zero.

Product: Excel
Device & OS: not provided
Scenario: Automating row insertion in a spreadsheet based on dynamic cell values using a macro.
Observed behavior: The user wants the macro to calculate the number of rows dynamically and insert them at a specific row, doing nothing if the calculation results in zero or a negative number.

Before you start

Ensure that you have enabled Developer options and allowed macros to run in your spreadsheet security settings before executing VBA code.

Solution 1Recommended

Use VBA to Insert Rows Dynamically Based on Cell Value

Apply a customized VBA macro to calculate the required row count and insert them at the designated row.

This VBA script reads the value from cell H9, subtracts 10, and if the result is greater than zero, inserts that exact number of rows starting at row 35.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click 'Insert' and then select 'Module' to create a new blank script window.

Paste the VBA Code

Enter the following code into the module window: Sub L() Dim n As Long n = Range("H9").Value - 10 If n > 0 Then Range("A35").Resize(n).EntireRow.Insert End If End Sub

Run the Macro

Close the VBA Editor, return to your spreadsheet, press ALT + F8, select 'L' from the macro list, and click 'Run'.

Google Sheets Compatibility: Google Sheets does not support VBA. To achieve this in Google Sheets, you must rewrite the logic using Google Apps Script.

Efficient Spreadsheet Automation

Automate Tasks with WPS Office VBA Support

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to automate repetitive tasks like inserting rows based on cell values with ease.

1. Open your File in WPS: Launch WPS Spreadsheet and open your .xlsm or .xlsx file.
2. Access the Developer Tab: Go to the 'Developer' tab on the top ribbon and click on the 'VBA Editor' icon.
3. Execute the Macro: Paste your macro code into the module and execute it to instantly insert the required rows based on your cell data.

Fully compatible with Microsoft Excel (.xls, .xlsx, .xlsm) formats and macros.Built-in VBA editor to write, edit, and debug macros directly.Lightweight software that runs scripts fast and smoothly.Free and comprehensive Office suite for all your document needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not inserting any rows?

If the value in cell H9 is 10 or less, the calculation (H9 - 10) results in zero or a negative number. The macro includes a condition (If n > 0) that prevents it from running when the result is not greater than zero.

Can I use this exact VBA code in Google Docs/Sheets?

No, Google Sheets does not support VBA. You will need to write an equivalent script using Google Apps Script to manipulate rows based on cell values in Google environments.

How do I change the macro to insert rows at a different location?

Modify the Range("A35") part of the code to reflect your desired starting cell. For example, to insert rows starting at row 20, change it to Range("A20").

Excel & Data Problems