• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto expandable Excel Table which is based on another Excel Table

inddon

Member
Hello There,

I have two worksheets having Table1 and Table2.

Note: Table2 is the formula reference of Table1.

Sheet1: Table1 has 3 columns and has 10 rows. 3 rows are filled and 7 rows are empty
Sheet2: Table2 has 3 columns and has 10 rows. 3 rows are filled and 7 rows are empty

What I do is the following:
1. I copy 100 rows data from another worksheet and paste it in worksheet Table1.
2. In worksheet Table2, after grouping the data from Table1 I expect 80 rows in Table2.

However, the worksheet Table2 does not get extended to 80 rows, it just stays at 10 rows. I have to manually extend the Table2.

Requirement: Based on the number of data that can fit, Table2 should expand and contract automatically. How can this be done?


Appreciate your help.

Many thanks
Don
 
Do you have access to PowerQuery (Get & Transform)? If so you can do Query operation from Table and do transformation at query level and import the transformed data to Sheet2.

This will be dynamic and will update with whatever the info is contained in Table1.

Alternately, it can be done via MSQuery but that will require additional steps (via vba or manual process) to update connection string as needed.
 
Do you have access to PowerQuery (Get & Transform)? If so you can do Query operation from Table and do transformation at query level and import the transformed data to Sheet2.

This will be dynamic and will update with whatever the info is contained in Table1.

Alternately, it can be done via MSQuery but that will require additional steps (via vba or manual process) to update connection string as needed.


Hi Chihiro,

Thank you for your reply. I have not used PowerQuery. I will keep that in the list to study.

In the mean time, I would like to get this work. If not with formulas than using VBA. With some help, I have written some VBA code to do the following. Could you please review and help in making it work?

1. Table3: Delete all the rows except the first row. It should keep all the formulas intact in the first row.
2. Based on the total number of rows in Table1, it should add rows in Table3. The code is giving an error as it needs manually to select the Table3.

I have attached the sample workbook for your reference.

Appreciate your help.

Thanks & regards,
Don



Code:
Sub TableDeleteInsertRows()

Dim WsRaw As Worksheet
Dim Tbl1 As ListObject
Dim Tbl1Count  As Integer

Dim WsFinal As Worksheet
Dim Tbl3 As ListObject
Dim Tbl3Count  As Integer

Dim i As Integer

'Setting worksheet and table1
Set WsRaw = Worksheets("RawData")
Set Tbl1 = WsRaw.ListObjects("Table1")

'Setting worksheet and table3
Set WsFinal = Worksheets("Final")
Set Tbl3 = WsFinal.ListObjects("Table3")

'-------------------------------------------------------
'Here I would like to delete all the rows from Table3 (Tbl3),
'except for the first row
'-------------------------------------------------------
Tbl1Count = Tbl1.DataBodyRange.Rows.Count
Tbl3Count = Tbl3.DataBodyRange.Rows.Count
Tbl3Count = Tbl3Count - 4
i = 0

'WsFinal.ListObjects("Table3").DataBodyRange.Select
Tbl3.ListRows(2).Range.Resize(Tbl3Count).Delete

'-------------------------------------------------------
'Here, based on the total number of rows in Table1, rows
'will be added in Table3
'eg. Table1 rows=30, it will add 30 rows in Table3
'somehow this is not working as the table needs to be selected
'-------------------------------------------------------
With WsFinal.ListObjects("Table3")
    Do Until Tbl1Count <= i
        Selection.ListObject(Tbl3).ListRows.Add AlwaysInsert:=True
        i = i + 1
    Loop
End With

Set Tbl1 = Nothing
Set Tbl3 = Nothing

Set WsRaw = Nothing
Set WsFinal = Nothing
End Sub
 

Attachments

  • Sample workbook - 1.xlsm
    23.6 KB · Views: 9
Thanks for good sample and starting code. Too few people do this, and it's appreciated.

See below for adjusted code with comments.
Code:
Sub TableDeleteInsertRows()
Dim wsRaw As Worksheet, wsFinal As Worksheet
Dim tbl1 As ListObject, tbl3 As ListObject
Dim tbl1Count As Integer, tbl3Count As Integer

'Setting worksheet and table1
Set wsRaw = Worksheets("RawData")
Set tbl1 = wsRaw.ListObjects("Table1")

'Setting worksheet and table3
Set wsFinal = Worksheets("Final")
Set tbl3 = wsFinal.ListObjects("Table3")

'Row count for later use
tbl1Count = tbl1.DataBodyRange.Rows.Count
tbl3Count = tbl3.DataBodyRange.Rows.Count

'-------------------------------------------------------
'Delete all but 1st row. Offset will adjust range down 1,
'resize is used to adjust delete range to row.count -1.
'Columns calculation is there just in case you have table
'with more than 1 column. If statement needed to avoid
'error when there is only 1 row in databodyrange
'-------------------------------------------------------
If tbl3Count > 1 Then
    tbl3.DataBodyRange.Offset(1, 0).Resize(tbl3Count - 1, _
    tbl3.DataBodyRange.Columns.Count).Rows.Delete
End If

'-------------------------------------------------------
'Instead of insert rows, resize table range. +1 to
'tbl1Count is used to include header row.
'-------------------------------------------------------
tbl3.Resize Range(Cells(1, 2), Cells(tbl1Count + 1, 2))

Set tbl1 = Nothing
Set tbl3 = Nothing

Set wsRaw = Nothing
Set wsFinal = Nothing

End Sub
 
Thank you very much Chihiro for your structured code. Also, for your complement :)

The code works good. I inserted a few columns in Table3. When I run the code, it deletes the newly added columns in Table3. Seems the below code is doing this.

Code:
tbl3.Resize Range(Cells(1, 2), Cells(tbl1Count + 1, 2))

Could you please advise, how can this be prevented? Attached the updated excel workbook for your reference.

Thanks & regards,
Don
 

Attachments

  • Sample workbook - 2.xlsm
    24.7 KB · Views: 8
Change the line to... this will adjust column# as long as table starts at cell B1.
Code:
tbl3.Resize Range(Cells(1, 2), Cells(tbl1Count + 1, 2 + tbl3.DataBodyRange.Columns.Count - 1))

You will need to adjust column/row reference if the top left cell of the table is in different cell.
 
Hi Chihiro,

I would like to keep the start of the table at A1, just for ease.


The below code works fine with table at A1. The only thing here is "Table3[#All]" is hard coded. How can this be replaced with tbl3 variable?


Code:
tbl3.Resize Range("Table3[#All]").Resize(tbl1Count + 1, tbl3.Range.Columns.Count)

Thank you & regards,
Don
 
Use something like below.
Code:
tbl3.Resize Range(Cells(1, 1), Cells(tbl1Count + 1,  tbl3.DataBodyRange.Columns.Count ))

See attached sample as well.

Edit: Or you can use...
Code:
tbl3.Resize tbl3.Range.Resize(tbl1Count + 1, tbl3.Range.Columns.Count)
 

Attachments

  • Sample workbook - 2.xlsm
    24.2 KB · Views: 21
Last edited:
Back
Top