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 :
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 :)