Forum Discussion
DataBodyRange Value Becomes "Nothing" After Resizing ListObject Table to 2
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 Sub
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
- 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.
