CODEWITHSUNDEEP
vbaexcel
Derek Harden
Derek Harden

Reputation: 41

Looping through a Case Statement

I am attempting to select multiple sheets and unhide all columns at once. I am using a Case Statement.

My script is successfully executing, however it skips over what I want to do "Columns.EntireColumn.Hidden=False.

I'm sure I am missing something minute and any help would be appreciated! I wrote the case via multiple lines and am guessing this may have something to do with the issue. Below is my code:

For Each ws In Worksheets
    Select Case ws.Name
    Case "Customer Service", "Document Production & Status", "Employment", _
         "Family & Business", "Humanitarian", "Refugee, Asylum, & DACA", _
         "NSC", "I-102", "I-824", "I-121 AP", "I-131 TD", "I-765", "I-485 EB", _
         "I-104", "I-140 Prem", "I-601A", "I-129", "I-129 Prem", "I-129F", _
         "I-130 IR", "I-130 All Other", "I-539", "I-360", "I-918", "I-192", _
         "I-821 TPS", "Wvrs (I-212)", "Wvrs (I-601)", "I-131 D", "I-485 Asy", _
         "I-485 Ref", "I-730", "I-765 D", "I-765 D Renewal", "I-765 D Renewal ELIS", _
         "I-821 D", "I-821 D Renewal", "I-821 D Renewal ELIS", "I-751", "N-565"

        Columns.EntireColumn.Hidden = False
    End Select
Next

Upvotes: 1

Views: 79

Answers (1)

Damian
Damian

Reputation: 5174

This should help:

Sub HideColumns()

    Dim wb As Workbook, ws As Worksheet, shts As Variant, Check As Variant

    Set wb = ThisWorkbook

    'store all your worksheet names on this array
    shts = Array("Customer Service", "Document Production & Status", "Employment", _
         "Family & Business", "Humanitarian", "Refugee, Asylum, & DACA", _
         "NSC", "I-102", "I-824", "I-121 AP", "I-131 TD", "I-765", "I-485 EB", _
         "I-104", "I-140 Prem", "I-601A", "I-129", "I-129 Prem", "I-129F", _
         "I-130 IR", "I-130 All Other", "I-539", "I-360", "I-918", "I-192", _
         "I-821 TPS", "Wvrs (I-212)", "Wvrs (I-601)", "I-131 D", "I-485 Asy", _
         "I-485 Ref", "I-730", "I-765 D", "I-765 D Renewal", "I-765 D Renewal ELIS", _
         "I-821 D", "I-821 D Renewal", "I-821 D Renewal ELIS", "I-751", "N-565")

    For Each ws In wb.Worksheets
        Check = 0
        Check = Application.Match(ws.Name, shts, 0) 'application won't raise an error if can't find the result, instead will give check an error value
        If Not IsError(Check) Then ws.Columns.EntireColumn.Hidden = False
    Next ws

End Sub

Upvotes: 1

Related Questions