Add/Remove a row when dynamic range is updated

[Juli Reid]

I have a workbook that uses a dynamic range to populate rows in a column on another worksheet.   This works as it should but because of the formatting of the surrounding cells (color, etc.), what I really need is for the change to trigger a row to be added or removed in a specific range of cells.

I would create a new dynamic range on the worksheet where the new rows need to be added/removed.  then I need to trigger a formula or macro where an change to the names in the first dynamic range cause a row to either be added or removed to in the second range of cells.

Edit:  In the master worksheet I added a cell that contains the count of rows in the MembersCount dynamic range.   I will be adding a range on the active worksheet called RowCount.   RowCount should always be one more than MembersCount.   So when the MembersCount changes I want to add a full row across or remove a full row at a specific location so that MembersCount is always one less than RowCount.

Example: 

• MembersCount is 4 and RowCount is 5.  MembersCount changes to 5 and RowCount is still 5.  A full row should be added above Monday!$b$8.  MembersCount is 5 and RowCount is now 6.
• MembersCount is 5 and RowCount is 6.  MembersCount changes to 4 and RowCount is still 6.  The row at Monday!$B$8 should be removed.  Members Count is 4 and RowCount is now 5.

[#2]

@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.

[#3]

Will it make any difference that the ranges are on two different worksheets?
MembersCount is on the Master (worksheet name) worksheet and RowCount is on the Monday (worksheet name)worksheet