excelvbaresizedatacolumn

Resize table using Listobjects not working


I have a table in Sheet 2 with a name "MyTable". Number of rows of that table changes each time depending on the data. I would like to clear the contents of the table and resize it using a macro so that it has only two rows- a title row, and an empty row.

Table title row is from B5 until K5.

I tried the below code, it clears the table contents and resizes, however, does not resize as desired. It resizes, without clearing the table borders in column C.

Any help is really appreciated.

Sub Table_Resize()

Dim rng as Range 

Sheet2.Select
Range("MyTable").ClearContents
        
Set rng = Range("MyTable[#All]").Resize(2, 10)
Sheet2.ListObjects("MyTable").Resize rng

End Sub

enter image description here


Solution

  • I think that what you are trying to do is to delete the all rows.

    Sub Table_ClearContents_Resize()
        Dim ws As Worksheet: Set ws = Sheets("Sheet2")
        Dim ol As ListObject: Set ol = ws.ListObjects("MyTable")
        
        ' Delete table contents
        ol.DataBodyRange.ClearContents
        
        ' Resize table
        ol.Resize Range(ol.HeaderRowRange.Resize(2).Address)
    End Sub
    

    enter image description here