VBA: Array Overwriting Totals Row and Existing Data below Table

[zero269]

Hello,

Is it possible to force an Excel Table to automatically expand based on the number of rows being added from an array?

Right now when I output the array, it's overwriting the data in the Totals Row, as well as the data that resides below the table.

The following code is what I was using to test the output into an empty sheet. However, when I modified it to add the new row and then output the array to that new row in column "A", it doesn't expand the table.

Sub WIP_Filter_New_Books()

'  Declarations
   Dim arrNewBooks, Student As String: Student = "Student007"

'  Store 'New' Books into Array
   arrNewBooks = Evaluate("SORT(CHOOSECOLS(FILTER(t_Books,t_Books[" & Student & "]=""n"",""""),1,2,4,5,6,7,9,10,11,12),2)")

'  Output Array
   Sheets("Sheet1").Range("A2").Resize(UBound(arrNewBooks), UBound(arrNewBooks, 2)) = arrNewBooks

End Sub

I realize now that this is the expected behavior, considering that if I use the drag method (lower right corner) it behaves the same way.
Perhaps someone has experience with outputting array data to Tables that need to be expanded… WITH data just below the Table?

Thank you and best regards,

 

[HongRu]

If I change the last line of @HongRu's code to the below, it works for me.

    Rng_tbl.Offset(N_tbl_rows + 1).Resize(N_newData, UBound(newData,2)) = newData

That code will fail if the Table has been cleared by deleting all the rows ie there is no DataBodyRange. Is that likely to ever be the case ?
Make the change below and that should cover it off.

    'Range("A" & tbl.DataBodyRange.Row + N_tbl_rows).Resize(N_newData).EntireRow.Insert
    Range("A" & tbl.Range.Row + 1 + N_tbl_rows).Resize(N_newData).EntireRow.Insert

Thank for your fix to my code.
This correction is very logical and more adaptable to all situations.

 

[Alex Blakenburg]

Hi Alex,

I tested your edit for HongRu's code. Incidently, I was getting the same results. See my two screenshots above.
At first I thought the +1 was going to insert below the Total Row, so I added some tags to the right of the Table to see what moved.
They both did the same thing to my surprise.

To make sense of the difference you need to know that:
• RangeObject.Row will only return the row number of the first row in the range
• ListObject.Range returns the whole table range including the headers and the totals
ListObject.DataBodyRange returns the range that lies between the Header and the Totals (or include the last row if there are no totals)

So
ListObject.Range.Row returns the header row and you need to add 1 to get the row below that, which is the same row you get with
LIstObject.DataBodyRange.Row

The trouble is that if you select all the rows in a table and delete them, the DataBodyRange no longer exists.
This is not that obvious since you will still see a blank row which is like a template row to let you start entering the data (the formula will not even be visible until you start entering data into the row). If you had merely cleared the contents when you only have one row, the formulas would still be visible.

Since the DataBodyRange doesn't exist "tbl.DataBodyRange.row" will error out.
ListObject.ListRows.Count = 0 would tell you it doesn't exist.

Table with all rows deleted - and activecell E3 in the formula column
This does NOT have a databodyrange

Table with all rows deleted EXCEPT row 1 where the content has been cleared- and activecell E3 in the formula column
Note: The formula and formula result are visible.
This has a databodyrange