Resizing a table to size 1 row

[krise]

Hello,

I am using a table to display data. So basically, I have a "Fill" VBA button that fills the table with data, the table size adapts to the size of the data, I already got that working. I also want to make a "reset" button, where it deletes everything in the table and resizes it back to row-size 1.
Currently I am using this function:

Sub reset()
    Worksheets("Sheet").ListObjects("table").DataBodyRange.ClearContents
    Worksheets("Sheet").ListObjects("table").Resize Range("table[#All]").Resize(2, 10)    '2 because 1 header row and 1 data row
End Sub

This works… almost. It resizes the table down to row-size 1, as I want, however, after using this function I cannot select the DataBodyRange attribute of my table anymore. When I try to run the code above twice, the second time fails with the error

object variable with block variable not set regarding the

Worksheets("Sheet").ListObjects("table").DataBodyRange

line. How can this be? Why can I no longer access the DataBodyRange of the table after resizing to 1?

Funnily enough, this functions, which is the same as above, with the only difference being it resizes to 2 rows instead of 1, works fine:

Sub reset()
    Worksheets("Sheet").ListObjects("table").DataBodyRange.ClearContents
    Worksheets("Sheet").ListObjects("table").Resize Range("table[#All]").Resize(3, 10)    
End Sub

I can call this how often I want and get no problems.

Can anyone explain to me whats going on here?

[Jan Karel Pieterse]

If a table has no data, DataBodyRange is "Nothing". So you should first test for the number of rows in the table:

Sub ClearTable()
    If Worksheets("Sheet1").ListObjects("table").ListRows.Count > 0 Then
        Worksheets("Sheet1").ListObjects("table").DataBodyRange.Delete
    End If
End Sub

[krise]

If a table has no data, DataBodyRange is "Nothing". So you should first test for the number of rows in the table:

Sub ClearTable()
    If Worksheets("Sheet1").ListObjects("table").ListRows.Count > 0 Then
        Worksheets("Sheet1").ListObjects("table").DataBodyRange.Delete
    End If
End Sub

Hi,
Thanks for the answer, you are correct, I get the error because my reset function apperantly deletes the DataBodyRange of the table, instead of just resizing it to 1.
Also your code works, I can call my Reset function twice now, however, it does not solve the underlying problem.

As mentioned in the question, I also have a "Fill Function", that just fills the table with data. I want to be able to call that function after I call the reset function. At the moment, this is of course not possible because my Reset function deletes the DataBodyRange for whatever reason, so my Fill function has nothing to write to. So is there a way to resize my table to 1 row WITHOUT deleting the DataBodyRange or is there maybe a way of creating a new DataBodyRange that the fill function can write to / that the reset function can delete?

[Jan Karel Pieterse]

What does your fill function look like?

[krise]

What does your fill function look like?

Sub Fill()  
    Set t = Worksheets("sheet").ListObjects("table").DataBodyRange
    data = transposeArray(cn.Execute("select * from some_table").GetRows)
    t.Resize(UBound(data, 1) - LBound(data, 1) + 1, UBound(data, 2) - LBound(data, 2) + 1) = data
    
End Sub

Where "transposeArray" is a custom transpose function because the standard VBA transpose has problems with null values. Cn is a ADODB.Connection that I declare somewhere else in my program, basically just a Database Connection

I want to add that this function does what I want it to, it resizes the table and fills it with data. Its just that after calling the Reset function this no longer works because of the above mentioned deleted DataBodyRange.

[Jan Karel Pieterse]

Use:

Sub Fill()
    Set t = Worksheets("sheet").ListObjects("table").Cells(2, 1)
    Data = transposeArray(cn.Execute("select * from some_table").GetRows)
    t.Resize(UBound(Data, 1) - LBound(Data, 1) + 1, UBound(Data, 2) - LBound(Data, 2) + 1) = Data
    
End Sub

[krise]

Use:

Sub Fill()
    Set t = Worksheets("sheet").ListObjects("table").Cells(2, 1)
    Data = transposeArray(cn.Execute("select * from some_table").GetRows)
    t.Resize(UBound(Data, 1) - LBound(Data, 1) + 1, UBound(Data, 2) - LBound(Data, 2) + 1) = Data
    
End Sub

Almost!

.Cells(2,1) does not work on tables, however, you had the right idea and I figured out that .Range(2,1) does the trick.

So this is the now working code:

Sub Fill()
    Set t = Worksheets("sheet").ListObjects("table").Range(2,1)
    Data = transposeArray(cn.Execute("select * from some_table").GetRows)
    t.Resize(UBound(Data, 1) - LBound(Data, 1) + 1, UBound(Data, 2) - LBound(Data, 2) + 1) = Data
End Sub

Thanks a lot for your help!

[Jan Karel Pieterse]

Very strange, I tested it and it worked on my machine.