Deleting table content apart from last 3 columns.

[Fredek]

Hi guys,

I can delete the entire content of the worksheet with the below code, however I have formulas in the last three columns. Is there a way I could keep the formulas but clear all the other cells of the table?

    Dim StrtSht As Worksheet
    Dim DestWbk As Workbook
Set StrtSht = ActiveSheet
    Set DestWbk = ActiveWorkbook
     Worksheets("Currency").ListObjects("GCMR_Data_tbl").AutoFilter.ShowAllData
    Worksheets("Currency").ListObjects("GCMR_Data_tbl").DataBodyRange.ClearContents

Many thanks,

 

[NoSparks]

Worksheets("Currency").ListObjects("GCMR_Data_tbl").ListColumns(1).DataBodyRange.Resize(, .ListColumns.Count - 3).ClearContents

 

[Fredek]

Hi NoSparks,

thank you for your response. Unfortunately, I am getting an Invalid or Unqualified reference error.

 

[NoSparks]

Oops...
the .ListColumns.Count needs to be fully qualified, same as .ListColumns(1), so it's referencing the same table.

Here's what I actually tested with

Sub testing()
    
    Dim oLo As ListObject
    
    Set oLo = Worksheets("Currency").ListObjects("GCMR_Data_tbl")
    
    With oLo
        .AutoFilter.ShowAllData
        .ListColumns(1).DataBodyRange.Resize(, .ListColumns.Count - 3).Select  'ClearContents
    End With
    
End Sub