krise
02-19-2020, 02:31 AM
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?
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?