VBA clear macro cleans more than the defined range

Question

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?

Possible duplicate of [How to select clear table contents without destroying the table?](https://stackoverflow.com/questions/10220906/how-to-select-clear-table-contents-without-destroying-the-table) — Wizhi, Nov 06 '18 at 08:40

score 1 · Accepted Answer · answered Nov 06 '18 at 11:49

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

VBA clear macro cleans more than the defined range

1 Answers1