delete rows without loosing the formula in excel u

Question

I have an excel sheet in which there is one table with a drop down and one normal excel cell. I try to clear the contents of the table and I have done it through

range("X").ClearContents

but the problem with it is it is going to clearing the content but I'm able to see the table borders with it.

When I have used

Range("X").Select
Application.DisplayAlerts = False
Selection.Delete
Application.DisplayAlerts = True
Range("X").Select
Selection.ClearContents

It deletes the table (both content and borders) but it I can see the formula in the drop down is missing ie the drop down cell has become normal cell.

Thanks in Advance!

Answer 1

You can use the ClearFormats() method, this will remove the table border.

Also, to clear contents, you do not have to first select the range. Take an advantage of the With statement

With Range("X")
    .ClearContents
    .ClearFormats
End With

The above should achieve what you're after.