Forum Discussion
Solved
DataBodyRange Value Becomes "Nothing" After Resizing ListObject Table to 2
I am an experienced programmer, but relatively new to Excel VBA. I have been working on creating a procedure to resize a table on a worksheet using the following code:
Public Sub ResizeTable(ATable As ListObject, NewSize As Long)
Dim CurrentSize As Long
CurrentSize = ATable.DataBodyRange.Rows.Count
If (CurrentSize <= 1) Then
Exit Sub
End If
With ATable
CurrentSize = .DataBodyRange.Rows.Count
.DataBodyRange.Borders.LineStyle = xlNone
Application.ScreenUpdating = False
.DataBodyRange.ClearContents
Application.ScreenUpdating = True
.Resize .Range.Resize(NewSize + 1, 5)
.DataBodyRange.Borders.LineStyle = xlContinuous
End With
End Sub
I have found that if a value of 1 is passed as the NewSize argument (i.e. to preserve the header row and a single data row) the DataBodyRange field of the ListObject has a value of "Nothing" and the statement following the resize statement (obviously) fails. If a value of 2 or greater is passed as the NewSize argument the DataBodyRange field is properly maintained.
This seems like a bug to me, but there may be something I am missing as a newbe to VBA.
Also, I don't quite understand how the line of code that performs the resizing operation works (I got it from an example I found on the web) with respect to using the intersection operator (space).
Any help would be appreciated.
Thanks for your time in replying to my question. I believe it is a bug in Excel that the DataBodyRange object is deleted when the DataBodyRange is cleared and then the ListObject table is resized to two rows (i.e. header row & one data row), but the DataBodyRange is not deleted if the table is resized to three rows (i.e. header row & two data rows).
A workaround for this problem is to immediately, after the resize operation, place and then remove a single space character in the first data cell of the table. This causes Excel to recreate the DataBodyRange object, allowing subsequent operations to be performed on the table (see code below).
I spent many, many hours tracking this idiosyncrasy (bug) down. I'm hoping someone at Microsoft will take notice and fix this issue.
Public Sub ResizeTable(ATable As ListObject, NewSize As Integer) Dim CurrentSize As Long With ATable CurrentSize = .DataBodyRange.Rows.Count If (CurrentSize < 1) Then NewSize = 1 End If .DataBodyRange.Borders.LineStyle = xlNone Application.ScreenUpdating = False .DataBodyRange.ClearContents Application.ScreenUpdating = True .Resize .Range.Resize(NewSize + 1, 5) ' The following two lines work around a "bug" in Excel that causes the ' DataBodyRange object to be deleted from the ' ListObject when the DataBodyRange is cleared (i.e. DataBodyRange.ClearContents) ' AND the table size is resized to 1 .Range.Cells(2, 1).Value = " " .Range.Cells(2, 1).Value = "" .DataBodyRange.Borders.LineStyle = xlContinuous End With End Sub
You clear the DataBodyRange. If you resize it to have only one row, that row becomes the "new" row of the table since it is empty. But the "new" row is not part of the DataBodyRange.
One workaround would be to exit if NewSize <= 1, just like you exit if CurrentSize <= 1.
Alternatively, don't clear the remaining rows of the DataBodyRange:
Public Sub ResizeTable(ATable As ListObject, NewSize As Long) Dim CurrentSize As Long Dim NumCols As Long Dim RemovedRange As Range With ATable CurrentSize = .DataBodyRange.Rows.Count NumCols = .ListColumns.Count If CurrentSize <= 1 Then Exit Sub End If Application.ScreenUpdating = False If NewSize < CurrentSize Then Set RemovedRange = Range(NewSize + 2 & ":" & CurrentSize + 1) With RemovedRange .Borders.LineStyle = xlNone .ClearContents End With End If .Resize .Range.Resize(NewSize + 1, NumCols) .DataBodyRange.Borders.LineStyle = xlContinuous Application.ScreenUpdating = True End With End SubThanks for your time in replying to my question. I believe it is a bug in Excel that the DataBodyRange object is deleted when the DataBodyRange is cleared and then the ListObject table is resized to two rows (i.e. header row & one data row), but the DataBodyRange is not deleted if the table is resized to three rows (i.e. header row & two data rows).
A workaround for this problem is to immediately, after the resize operation, place and then remove a single space character in the first data cell of the table. This causes Excel to recreate the DataBodyRange object, allowing subsequent operations to be performed on the table (see code below).
I spent many, many hours tracking this idiosyncrasy (bug) down. I'm hoping someone at Microsoft will take notice and fix this issue.
Public Sub ResizeTable(ATable As ListObject, NewSize As Integer) Dim CurrentSize As Long With ATable CurrentSize = .DataBodyRange.Rows.Count If (CurrentSize < 1) Then NewSize = 1 End If .DataBodyRange.Borders.LineStyle = xlNone Application.ScreenUpdating = False .DataBodyRange.ClearContents Application.ScreenUpdating = True .Resize .Range.Resize(NewSize + 1, 5) ' The following two lines work around a "bug" in Excel that causes the ' DataBodyRange object to be deleted from the ' ListObject when the DataBodyRange is cleared (i.e. DataBodyRange.ClearContents) ' AND the table size is resized to 1 .Range.Cells(2, 1).Value = " " .Range.Cells(2, 1).Value = "" .DataBodyRange.Borders.LineStyle = xlContinuous End With End Sub- The solution works perfectly. Thank you!
Am working with 7 tables all stored into the same worksheet. I was experiencing the same issue for the last couple of days. Each time I had to reset the table(s) manually. Had almost quit the idea of working with tables, although otherwise, they make life much easier. So glad to have found this lifesaver during my search for answers.
