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

[Codewarrior3]

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.

[#2]

@Codewarrior3 

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

[#3 · Accepted solution]

@Hans Vogelaar 

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

[#4]

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.