Clear Contents from Specific Hidden Tabs

[rkeen0822]

Good Morning,
Looking to shorten up the code to clear out the contents from about 90 tabs. Tab Names are numbered for each data pull (i.e. Hour 1, Hour 2, Hour 3, Line 1, Line 2, Line 3, Item 1, Item 2, Item 3 ....etc). Each category goes to 13.

I don't want to use the easy "Clear from all Hidden" as there are some hidden tabs that are meant for calculations that I don't want to clear. Any assistance and advice would be great.

Thanks in advance.

 

[DanteAmor]

Hi @rkeen0822. Thanks for posting on the forum.

I can see that the names of your categories have a space and then the number.

So with the following code we check only the left part of the sheet name.
It only captures the categories in the Array and the Range of cells to Clear.

If it's all cells then change this:

sh.Range("A2:C4").ClearContents

to this:

sh.Cells.Clear

Try this code:

Sub ClearContentsSpecificHiddenTabs()
  Dim sh As Worksheet
  Dim cats As Variant
  Dim sName As String
  
  cats = Array("Hour", "Line", "item", "etc")   'Here the names of the categories
  
  For Each sh In Sheets
    sName = "|" & Split(sh.Name, " ")(0) & "|"
    If InStr(1, "|" & Join(cats, "|") & "|", sName, vbTextCompare) > 0 Then
      sh.Range("A2:C4").ClearContents           'Fit the range of cells to clear
    End If
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[rkeen0822]

Hey Dante,

thanks for the reply. this cleaned up well and will be adding this to my code bank for clearing content.

Thanks