Resize a table with formulas based on table input-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

The OP (see comments) is using PowerQuery to extract a table from a SQL source. An alternative to re-sizing the table via VBA is to simply amend the original PowerQuery code to include the additional Id column. Then re-sizing will happen automatically when the query is refreshed.

In the PowerQuery Editor, you can add in a Custom column which contains a formula.

You can see the equation is:

=[CoCd] & "_" & Number.ToText([LSCH]) & "_" & Number.ToText([Material]) & "_" & Number.ToText([Month])

The string concatenation operator '&' only works on strings, so since my example data has numbers, I am using Number.ToText() to convert. Depending on the exact datatypes in the SQL source, this might not be necessary.

Next, in the Editor, drag the newly created 'Id' column from right to left. This will generate a 'Reordered Columns' line in the 'Applied Steps' list.

Finally, 'Close & Load' from the Home menu, and you should get this output in your sheet:

If you just want a subset of your SourceTable query in a table called ProductList, then you can set up a PowerQuery internally within the workbook to produce the new table from the source table, and then refresh that (you can set up a dependency on SourceTable so that will be refreshed first).