CODEWITHSUNDEEP
vbaexcelexcel-2007
S K padala
S K padala

Reputation: 251

delete rows without loosing the formula in excel using vba

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!

Upvotes: 1

Views: 1417

Answers (1)

luke_t
luke_t

Reputation: 2985

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.

Upvotes: 2

Related Questions