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?

 

[Comfy]

This will retain the formulas etc:

Sub deleteRows()
Dim LO As ListObject


Set LO = ActiveSheet.ListObjects(1)


LO.DataBodyRange.Rows.Delete


End Sub

 

[nmgmarques]

This will retain the formulas etc:

Sub deleteRows()
Dim LO As ListObject


Set LO = ActiveSheet.ListObjects(1)


LO.DataBodyRange.Rows.Delete


End Sub

I just tested it but sorry to say it deleted the formulas.

 

[Comfy]

That would suggest to me then that your formula columns are not set as Calculated Columns??

 

[nmgmarques]

That would suggest to me then that your formula columns are not set as Calculated Columns??

Ok, now you just lost me :P To the Googlemobile.

/reads furiously!

Ok I think I understand and I think they are calculated columns. In any case, this is the file in question. You can check it and let me know if I'm not understanding it correctly. You'll have to pause the VBA scripts to access the columns that are currently being jumped over.
The file.

 

[Comfy]

Your Table does retain the formulas.

Once you ran the macro did you enter a value into column B to see if the VLookup worked?

 

[nmgmarques]

Your Table does retain the formulas.

Once you ran the macro did you enter a value into column B to see if the VLookup worked?

Yes, but it's not running the vlookup on my side and I see no formula in the cell when I select it.
Screen Grab here.

 

[Comfy]

Do me a favour, do the same thing as in the clip but once you enter a No Infor. Press F9

Does the VLookup populate?

If so, try changing calculations to auto.

 

[nmgmarques]

Do me a favour, do the same thing as in the clip but once you enter a No Infor. Press F9

Does the VLookup populate?

If so, try changing calculations to auto.

****, you are good. I was actually just popping over here to tell you that I had just noticed that for some reason it seems that my calculation options change to manual each time I press the button. Can't figure out why. But in any case, that was indeed the issue. It's all sorted out now. Just have to figure out why it keeps disabling the auto and setting to manual.

 

[Comfy]

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?