Is there a way to clear the table (listObject) content and resizing it one column?

user_16347892 0
Viewed 239

I wish to reset a table (listObject) via VBA. I tried it in two ways:

Idea 1: Clear table content and resize the table to one column

 With ActiveWorkbook.Worksheets("Sheet1").ListObjects("tblExample")
        If Not .DataBodyRange Is Nothing Then .DataBodyRange.ClearContents
        Dim numColumns As Long: numColumns = .ListColumns.Count
        If numColumns > 1 Then
               .DataBodyRange.Offset(1).Resize(, .Columns.Count - 1).Delete 'Last statement
        End If
End With

The problem: When evaluating the last statement I get the runtime '438' error stating something like: 'Object does not support this Property or Method.'

Idea 2: Delete the table and make a new one

With ActiveWorkbook.Worksheets("Sheet1")
If .ListObjects.Count = 1 Then
           .ListObjects(1).Delete
           .ListObjects.Add(Destination:=.Range("I1:I105")).Name = "tblExample"
End If
End With

The problem: The new table is placed at a random location (= selected cell).

vba excel
1 Answers
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("tblExample")
    .Resize .Range.Resize(ColumnSize:=1)  ' resize entire table to only first column
End With

ListObjects("tblExample").Resize expects the new range of the table.

ListObjects("tblExample").Range gives the old range of the table and .Resize(ColumnSize:=1) resizes the old range to only one column.

Are you sure you mean ActiveWorkbook?

  • ActiveWorkbook the workbook that is on top (has focus).
    can be any workbook the user clicks on while the macro is running.
  • ThisWorkbook the workbook the VBA code is written in.
    is a defined workbook that never changes.

In most cases you should avoid ActiveWorkbook as it can change by a single mouse click and use ThisWorkbook.

user_32196130
Related