DataBodyRange Value Becomes "Nothing" After Resizing ListObject Table to 2

HansVogelaar 

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