Clearing table without destroying formulas

[nmgmarques]

Hi all.

Trying to clear Table1 in Worksheet "Análise" without destroying my formulas or existing data validation. This is the original look.

So far I have tried:

Sub ClearTables()
    With Worksheets("Análise").ListObjects("Table1")
        If Not .DataBodyRange Is Nothing Then
[B]            .DataBodyRange.SpecialCells(xlCellTypeConstants).ClearContents[/B]
        End If
    End With
End Sub

This clears only cells without formulas and doesn't destroy data validation, but doesn't delete rows nor blank out the cells with formulas.

Also tried:

.DataBodyRange.ClearContents

This cleared the entire table, left the data validation, but destroyed the formulas and didn't delete empty rows.

And of course:

.DataBodyRange.Delete

This one is the closes I got. Deleted all rows except first, leaving it empty. Didn't remove data validation, but did destroy the formulas.

Can anyone suggest a way to clear, delete empty rows, but keep formulas and validation in first row?

 

[nmgmarques]

You're Welcome.

I can't see anything in your code that would change it.

This setting is Application wide so do you have any other workbooks open?

No other files were open as you can see in the video. I couldn't get it to work but soon realized this was happening in all my files that had macros in them. After stressing out... I finally logged off the server session and kicked myself off to make sure I logged back into a new server. Now all is working perfectly. Probably some CPU brain freeze or whatever.

I'd like to thank everyone for taking the time to help me with this. You guys are great!