Add/Remove rows to table

[csotstot]

Good morning all...
I'm running into the "This won't work because it would move cells in a table..." issue. I'm building an Excel report (can't use Power BI at this point) that requires two tables on a sheet tab, one above the other. Both tables are generated by Get and Transform queries that place them on the sheet tab.
With this format, I can't add (or presumably) remove rows from the top table without generating the error. I'm not sure why separating the tables by enough rows to ensure that the top table won't grow enough to move the bottom table (since the tables are generated with absolute references) doesn't work, but I'm presuming Excel wants to retain the same separation.
I can work around the problem by generating the bottom table to the side and then moving it to the desired position after running the query refresh, but this is obviously going to be a bit kludgy for my end-user (my boss) to use as a solution.
I'm looking to see if there is a reasonably simple method (due to my limited VBA knowledge) to have the tables generate as desired without manual intervention. Once I've got the method down, then I can build a new template for him to use...

Thanks!

 

[Matt Allington]

Why do you need to 2 tables on the same sheet? What is the relationship between the data in the 2 tables?

 

[csotstot]

Why do you need to 2 tables on the same sheet? What is the relationship between the data in the 2 tables?

Thanks for the question Matt...
It's a matter of being directed to have the two tables appear on the same sheet; they aren't related in any fashion. It's simply a report that provides a list of weekly activities by the dept manager in the top table and a list of in-progress procurements being tracked by dept staff in the second table.
The format is what the dept manager's supervisor was used to seeing with everything being manually entered into a spreadsheet each week. The report is reading entries in a couple of SharePoint lists and simply dumping them to tables; it's a case of "this is what we're used to seeing and the way we want to keep seeing..." I'm having to pull everyone along with baby steps to more updated reporting and analysis through BI tools...

 

[Matt Allington]

what version of Excel are you using? I just tested this with Excel 2016 O365 and it worked fine. See video https://www.dropbox.com/s/owoqsfcoasvt1c5/growing table.wmv?dl=1

 

[csotstot]

what version of Excel are you using? I just tested this with Excel 2016 O365 and it worked fine. See video https://www.dropbox.com/s/owoqsfcoasvt1c5/growing table.wmv?dl=1

I'm using the same (64-bit); I'll take a look at your video this evening (dropbox is blocked at my office)...

Thanks!
Charlie

 

[My Aswer Is This]

Can you not clear the ranges in the Table as you want and not delete the rows

Such as:

This script clears the Tables last row of data.

Sub Clear_Table_Row()
'Modified  11/5/2018  4:22:51 PM  EST
Application.ScreenUpdating = False
With ActiveSheet.ListObjects("Sally").DataBodyRange
    ans = .Rows.Count
    .Cells(ans, 1).Resize(, .Columns.Count).ClearContents
End With
Application.ScreenUpdating = True
End Sub

 

[peter789]

I can only reproduce this if the top table is narrower than the one below. If this is the case the solution might be to go to External Data Properties and change the option to Insert entire rows for new data etc., rather than insert cells.

Peter

 

[csotstot]

I can only reproduce this if the top table is narrower than the one below. If this is the case the solution might be to go to External Data Properties and change the option to Insert entire rows for new data etc., rather than insert cells.

Peter

Thanks Peter!
That is precisely the scenario I am facing. My apologies to all, I never even considered the difference in the number of columns per table. Changing the external data properties permits the tables to refresh as expected!

Thanks to everyone for the assistance!

Charlie