excelvbaexcel-tableslistobject

Is there a way to clear content in a dynamic range below a table?


I have 3 tables on one sheet and right now I have vba for resizing tables 2 and 3 whenevever table 1 is resized, but would it be possible to clear all the contents below tables 2 and 3 when they are resized, because the formulas are left over

Here's the resize code :

Dim Tbl_2 As ListObject
Dim Tbl_1 As ListObject
Dim Tbl_3 As ListObject

Set Tbl_1 = Sheet1.ListObjects("TableQuery")
Set Tbl_2 = Sheet1.ListObjects("Table2")
Set Tbl_3 = Sheet1.ListObjects("Table3")

If Tbl_3.Range.Rows.Count <> Tbl_1.Range.Rows.Count Then
    Tbl_3.Resize Tbl_3.Range.Resize(Tbl_1.Range.Rows.Count)
End If

If Tbl_2.Range.Rows.Count <> Tbl_1.Range.Rows.Count Then
    Tbl_2.Resize Tbl_2.Range.Resize(Tbl_1.Range.Rows.Count)
End If

And here's the picture of what I want to ideally remove once the table is resized :

image showing leftover formulas after table resizing


Solution

  • I have adjusted your code a bit as I wasn't sure if I was allowed to change both tables at the same time or if you might place certain things in between those tables so I made an extra sub to make it shorter(ish). This will make sure it only affects the cells it had prior to the resize (since you're only resizing the rows, the columns remain the same)

     Sub clearRowsAfterResizing()
        Dim Tbl_2 As ListObject
        Dim Tbl_1 As ListObject
        Dim Tbl_3 As ListObject
        Dim ws As Worksheet
        
        Set ws = ActiveWorkbook.Worksheets("Test2")
        Set Tbl_1 = ws.ListObjects("TableQuery")
        Set Tbl_2 = ws.ListObjects("Table2")
        Set Tbl_3 = ws.ListObjects("Table3")
        
        changeSize Tbl_2, Tbl_1, ws
        changeSize Tbl_3, Tbl_1, ws
    
     End Sub
     
     Sub changeSize(tblAdjust As ListObject, tblChanged As ListObject, ws As Worksheet)
        Dim lRow As Long, dif As Long, sCol As Long, lCol As Long
        lRow = tblAdjust.Range.Rows(tblAdjust.Range.Rows.Count).Row
        dif = tblAdjust.Range.Rows.Count - tblChanged.Range.Rows.Count
        If tblAdjust.Range.Rows.Count <> tblChanged.Range.Rows.Count Then
            tblAdjust.Resize tblAdjust.Range.Resize(tblChanged.Range.Rows.Count)
            If dif > 0 Then
                sCol = tblAdjust.Range.Columns(1).Column
                lCol = tblAdjust.Range.Columns(tblAdjust.Range.Columns.Count).Column
                With ws
                    .Range(.Cells(lRow - dif + 1, sCol), .Cells(lRow, lCol)).Clear
                End With
            End If
        End If
     End Sub
    

    Hope this helps, if you have any questions feel free to ask :)