Resize a table with formulas based on table input

Question

I want to resize the table "ProductList" on my worksheet "Final" depending on the input from the source table "SourceTable" on the worksheet "Input" (fields derived from source table helped with formulas).

Eg.: Firstly, I want to refresh source table, then I want to resize the Table "ProductList" that has the range F1:J4 located on the sheet "Final" accordingly to the source table on the worksheet "Input" that has the current range A1:D7 growing monthly just by adding rows.

Help will be greatly appreciated.

Answer 1

Resize an Excel Table

• This will adjust the number of rows of the source table to the number of rows of the destination table.
• If the destination table has more rows than the source table, the excessive rows will be deleted.
• If the destination table contains formulas and has fewer rows than the source table, the newly added cells will update accordingly.

Option Explicit

Sub SizeTable()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Input")
    Dim stbl As ListObject: Set stbl = sws.ListObjects("Input")
    Dim srCount As Long: srCount = stbl.Range.Rows.Count
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("Final")
    Dim dtbl As ListObject: Set dtbl = dws.ListObjects("ProductList")
    Dim drCount As Long: drCount = dtbl.Range.Rows.Count
    
    If drCount > srCount Then
        dtbl.Range.Resize(drCount - srCount).Offset(srCount).Delete
    End If
    
    dtbl.Resize dtbl.Range.Resize(srCount)
    
End Sub