Using IF with Or to Loop through tables

[Brandi]

I am needing to Delete the rows of multiple tables in a file (leaving the Formulas intact), but I need tables on a few tabs to remain completely intact. When I use the following macro, it works if I cite One exception, but if I add an Or statement, it ignores the tabs listed after Or and only protects the first Tab listed.

The Code Is:
>>> use code - tags <<<

Sub DeleteRows()

    Dim tbl As ListObject
    Dim xWs As Worksheet
    On Error Resume Next
   
        For Each xWs In ThisWorkbook.Worksheets
            If xWs.Name <> "Continuous" Then
            For Each tbl In xWs.ListObjects
           
                Set tbl = ActiveSheet.ListObjects()
                tbl.DataBodyRange.Rows.Delete
              
               tbl.ShowTotals = True
              
        Next tbl
        End If
    Next xWs
   
End Sub

The Sample File I have attached only has four tab (there are Many in the original, but I need to skip 3 of them for this macro). There are more tabs to delete data from than tabs to leave intact. Most tabs in the original file only have the one table on them, but the last tab has Multiple tables on them. My example only has one table per tab, but I wanted to explain that, just in case.

Any thoughts on how to handle?

Thanks!

 

[vletm]

Brandi
Something like this
... including Your weekend saving option.

 

[Brandi]

Brandi
Something like this
... including Your weekend saving option.

I couldn't get that to work? The two highlighted in yellow I need to delete the data in those.

 

[vletm]

Brandi
... hmm?
I did two copies ... minor modification ... and tested with those copied sheets.
Do it work now?
It would matter in which part should name those sheets.
I hide that other option for use or for clear away.

 

[Brandi]

@vletm
You are Amazing! Again, I cannot thank you enough!
That worked perfectly.

 

[vletm]

Brandi
I continue wondering Your ActiveSheet in that part of code ... hmm?
Is that sheet really active while deleting?
Would You test this version too?

 

[Brandi]

Brandi
I continue wondering Your ActiveSheet in that part of code ... hmm?
Is that sheet really active while deleting?
Would You test this version too?

It worked, but it gave me the pop up for each table it modified. The first one worked without that, but they are deleting the Formulas in those tables as well. I have the formulas protected, but that doesn't seem to save them in this case.

Is there a way to delete the unprotected parts of the table and leave the columns with formulas intact?

 

[vletm]

Brandi
...hmm?
You wanted to delete DataBodyRange.Rows ... but You do not want to delete ... those?
Where are You worried formulas?

 

[Brandi]

@vletm
In the sample file, the L column on Special is a formula and the R column on the Bulk is a formula. But on the actual file, there are many columns in each file that has formulas. The original code I had did not delete the formulas, but I couldn't get it to loop through and skip tables.

 

[vletm]

Brandi
I see ... that my used userinterfaceonly allows more to do ...
Then ... hmm?
... one, so far, an idea
1) copy&paste the top row in 'safe' ... few rows below that table?
2) delete DataBodyRange.Rows as now
3) copy&paste cells which has formulas back to top row
4) delete that 'safe'-row
You can do it

 

[Brandi]

@vletm
I tried this:

>>> use - code tags <<<

Sub DeleteRows()
    Dim tbl As ListObject
    On Error Resume Next
    For xWs = 1 To Worksheets.Count
        Select Case Sheets(xWs).Name
'   here known sheetnames
           Case "Special", "Batch", "Invoice Top Copy", "Raw_Daily_Production", "Battery_Production", "Truck_Treat", "DIF_Batch", "DIF_Continuous"
                For Each tbl In Sheets(xWs).ListObjects
                    Set tbl = Sheets(xWs).ListObjects()
                    Sheets(xWs).Cells.SpecialCells(xlCellTypeConstants).ClearContents
                    tbl.ShowTotals = True
                    If Err.Number <> 0 Then Err.Clear
                Next tbl
'   or those names here, if this side works smoother
'            Case Else
'                x = x
        End Select
    Next xWs
End Sub

Which worked, but it did delete the Table Headers for some reason?
I will try your solution now.

Thank You for all of your help!!

 

[vletm]

Brandi
Do each sheet has one table?
... now it tries to work with many tables per sheet.
Some info about tables:

The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Sub ResetTable()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
'Delete all table rows except first row
  With tbl.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With
'Clear out data from first table row (retaining formulas)
  tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
End Sub

 

[Brandi]

Brandi
Do each sheet has one table?
... now it tries to work with many tables per sheet.
Some info about tables:

The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Sub ResetTable()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
'Delete all table rows except first row
  With tbl.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With
'Clear out data from first table row (retaining formulas)
  tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
End Sub

Sorry, I got side tracked due to a call from my son. Unfortunately he has Covid, so I was making sure he was all taken care of.
I made the adjustment to the code and that does work and is maintaining the headers now.
Just created the next issue that with all rows getting deleted from a table, it is only leaving the data for row one and once they paste in the new data, only row one has the formula retained.
That seems odd, since it is a table? I thought the formulas would automatically carry down the column?

 

[vletm]

Brandi - Is You son better soon?
Formulas ... I normally don't use formulas if possible ...
But I continued ...
Did You tested that my sample code?
I ... tested ... I got feeling that this version would work for You.
... those formulas stays alive!
There is one major modification
... You name those fixed = non-deleting tables names instead of which to delete

 

[Brandi]

@vletm
He should be fine. He is in the military and they are taking care of him. He just has fever and doesn't feel good right now, but should recover fine. He is a healthy young man.

Your fix worked Perfect! That is what I needed!
Thank you so much for your help and teaching me so much the last few days. Not only have you helped me, but many field guys will be so happy to not have to do these repetitive tasks each time.
Thank You!

 

[Brandi]

@vletm
Is it possible to Clear the Contents, leave the formulas and not resize the table? So, Not Delete the rows?

 

[vletm]

Brandi
A healthy young man would be back soon ... think positive.

Why do You want to change Your plan with those rows?
... if there are ... a lot of rows ... and ... left those rows there, then many field guys could fill their data anywhere
... it would many times more clear to have there only needed rows ... hmm?
I got one note ... which seems missing here ... that You would need unprotect some sheets?
... have You tried to modify protection, if there would add some feature which PowerQueries needs? (I don't use that at all)

or https://www.thebiccountant.com/2020/11/18/how-to-refresh-power-queries-on-protected-sheets-in-excel/