Dec 29 2022 12:47 PM
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.
Dec 29 2022 01:46 PM
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
Dec 31 2022 10:34 AM
Solution
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
Apr 24 2023 03:10 PM
Dec 31 2022 10:34 AM
Solution
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