Q:

VBA clear macro cleans more than the defined range

I have data in Excel which is in table format, half of which (columns) contains data and the rest contains some formulas. I am writing some results on the data part, so I want to delete it before each update.
I tried to delete data using a VBA code, which contains similar piece of codes as below:
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

However, it deletes the whole table. I tried various version of this code, but did not work. Also, I realized that, when the data is not in table format the macro works correctly. However, I need it to be a table to update the formulas in a dynamic range.
Does this error is related to table format & any ideas how can I fix it?

A:

You need to use the .DataBodyRange property of the ListObjects object (this is what tables are called in VBA). The code below will clear the non formula elements of a table that is on the active sheet.
Sub ClearTableButLeaveFormulas()

With ActiveSheet.ListObjects(1)
   .DataBodyRange.SpecialCells(xlCellTypeConstants).ClearContents
End With

End Sub

