Add/Remove a row when dynamic range is updated

Juli Reid 

To achieve this functionality, you can use a Worksheet Change event macro in VBA.

With the information you have provided you could try out the following code.

Here's a step-by-step guide on how to set it up:

1. Open your Excel workbook.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. In the Project Explorer window, locate the worksheet where the first dynamic range (MembersCount) is located.
4. Double-click on that worksheet to open its code window.
5. Paste the following VBA code into the code window:

Vba code is untested, please backup your file.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MembersCount As Range
    Dim RowCount As Range
    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = ThisWorkbook.Worksheets("YourWorksheetName") ' Change "YourWorksheetName" to the name of your worksheet
    
    ' Define the ranges
    Set MembersCount = ws.Range("MembersCount") ' Change "MembersCount" to the address of your first dynamic range
    Set RowCount = ws.Range("RowCount") ' Change "RowCount" to the address of your second dynamic range

    ' Check if the change occurred within the MembersCount range
    If Not Intersect(Target, MembersCount) Is Nothing Then
        Application.EnableEvents = False ' Disable events to prevent triggering the change event recursively

        ' Calculate the difference between MembersCount and RowCount
        LastRow = RowCount.Row + RowCount.Rows.Count - 1
        If MembersCount.Value > RowCount.Value Then
            ' Add rows above the specified cell
            ws.Rows(LastRow).Resize(MembersCount.Value - RowCount.Value).Insert Shift:=xlDown
        ElseIf MembersCount.Value < RowCount.Value Then
            ' Delete rows above the specified cell
            ws.Rows(LastRow - (RowCount.Value - MembersCount.Value)).Resize(RowCount.Value - MembersCount.Value).Delete
        End If

        Application.EnableEvents = True ' Re-enable events
    End If
End Sub

1. Modify the following parts of the code to fit your specific workbook:

• Replace "YourWorksheetName" with the name of your worksheet containing the dynamic ranges.
• Replace "MembersCount" with the address of your first dynamic range.
• Replace "RowCount" with the address of your second dynamic range.

1. Close the VBA editor and save your workbook as a macro-enabled file (.xlsm).
2. Now, whenever there's a change in the MembersCount range, the macro will automatically add or remove rows in the specified range (RowCount) to ensure it's always one more than MembersCount. The text, steps and the code were created with the help of AI.

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.