VBA & Macro Problems

Automatically Insert or Delete Excel Rows When a Range Changes with VBA

Aamir Naveed Akram Nov 12, 2026 869 views

Question details

Create a worksheet VBA event that automatically adds or removes rows when the MembersCount range changes, keeping the controlled row count one greater than the member count.

Automatically Insert or Delete Excel Rows When a Range Changes with VBA

Product: Microsoft Excel VBA worksheet macro
Device & OS: not provided
Scenario: The workbook uses named ranges called MembersCount and RowCount, and the number of rows must adjust after a user edits the member count.
Observed behavior: The target row area should contain MembersCount plus one row after the count is changed.

Before you start

Confirm that MembersCount and RowCount are valid workbook or worksheet named ranges and that RowCount identifies the row area to resize. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) before adding or running the event code.

Solution 1Recommended

Add a Worksheet_Change event to resize rows automatically

Use this method when users directly edit the MembersCount cell and the worksheet must insert or delete rows immediately.

Place the code in the code module for the worksheet that contains the count cells, not in a standard VBA module. The macro temporarily disables events so that row updates do not trigger the event repeatedly.

Open the worksheet code module

In Excel, press Alt+F11 to open the Visual Basic Editor. In Project Explorer, expand Microsoft Excel Objects and double-click the worksheet that contains MembersCount and RowCount.

Paste the worksheet event code

Paste this code into that worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim members As Range, rowCount As Range Dim ws As Worksheet Dim difference As Long Set ws = ThisWorkbook.Worksheets("YourWorksheetName") Set members = ws.Range("MembersCount") Set rowCount = ws.Range("RowCount") If Intersect(Target, members) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False difference = CLng(members.Value) + 1 - CLng(rowCount.Value) If difference > 0 Then ws.Rows(rowCount.Row + rowCount.Rows.Count - 1).Resize(difference).Insert Shift:=xlDown ElseIf difference < 0 Then ws.Rows(rowCount.Row + rowCount.Rows.Count - 1 + difference).Resize(-difference).Delete End If CleanUp: Application.EnableEvents = True End Sub

Update the worksheet and range names

Replace "YourWorksheetName" with the actual worksheet tab name. Replace MembersCount and RowCount if your workbook uses different named ranges; use Formulas > Name Manager to verify the names.

Save and test the macro

Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Return to the worksheet, enable macros if prompted, and edit the MembersCount cell to verify that the required rows are inserted or deleted.

Add a Worksheet_Change event to resize rows automatically

Worksheet_Change runs after direct edits: If MembersCount is calculated by a formula, Worksheet_Change may not run when its formula result changes. In that case, use a Worksheet_Calculate event or trigger the resizing procedure from the macro that updates the source values.

Solution 2

Use Worksheet_Calculate when the member count is formula-driven

Use this method if MembersCount changes because formulas recalculate rather than because a user types directly into the count cell.

Automate spreadsheet row updates with WPS Office

Use WPS Spreadsheets to manage macro-enabled row adjustments

WPS Spreadsheets provides a familiar worksheet environment for opening macro-enabled workbooks and managing the named ranges, rows, and VBA-based automation used for dynamic member lists. Open the XLSM workbook, verify the count ranges, and test the row-adjustment macro in the desktop app.

1. Open the macro-enabled workbook: Launch WPS Spreadsheets and select Menu > Open to open the XLSM workbook containing the member list.
2. Check the named ranges: Use the Name Manager or the name box beside the formula bar to confirm that MembersCount and RowCount refer to the intended cells or row area.
3. Review the worksheet macro: Open the macro or VBA tools available in the desktop version of WPS Office and confirm that the event code is stored in the correct worksheet module.
4. Enable and test automation: Enable macros only for a trusted workbook, then change the MembersCount value and verify that the expected rows are added or removed.

Manage dynamic rows and named ranges in WPS Spreadsheets.Open and edit Microsoft Excel XLSX and XLSM workbook formats.Use a familiar spreadsheet interface for cells, rows, and formulas.Lightweight office suite for continuing spreadsheet work across common document formats.

Frequently Asked Questions

Where should I place the Worksheet_Change code?

Place it in the code module for the specific worksheet containing MembersCount and RowCount. In the Visual Basic Editor, double-click that worksheet under Microsoft Excel Objects before pasting the code.

Why does the macro not run when the value in MembersCount changes?

Worksheet_Change runs when a user directly changes a cell value. If MembersCount is a formula result, use Worksheet_Calculate or run a dedicated resizing macro after the source data changes.

Why must Application.EnableEvents be set to False?

Inserting or deleting rows can trigger additional worksheet events. Setting Application.EnableEvents to False prevents the event procedure from calling itself repeatedly; the cleanup section turns events back on.

What file type is required for this automatic row macro?

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). A standard *.xlsx workbook does not retain VBA code.

How do I change the code for different named ranges?

Replace "MembersCount" and "RowCount" in the Set members and Set rowCount lines with your own defined names. You can inspect or edit defined names from Formulas > Name Manager in Excel.

Excel & Data Problems