Looking for some help on hiding columns based on checkbox selection

Hi there.

I have a row with headers (Row 2) and each header is 3 columns in length with information about the header we can call "cities" for now. I have a userform where a user selects which cities he/she wants to view the data for. What i would like is to be able to hide all other cities that the user did not check. I currently have code that does what i need it to however, it is very slow in calculating and takes 10 seconds to load. Looking to hopefully find an alternative to shorten that calculation time.

Private Sub CommandButton2_Click()
Dim icell As Range, siterng As Range, rng1 as Range

Range("SiteList").EntireColumn.Hidden = True
      Set rng1 = Union(Range("I:I"), Range("L:L"), Range("O:O"), Range("R:R"), Range("U:U"), Range("X:X"), Range("AA:AA"), Range("AD:AD"), Range("AG:AG"), Range("AJ:AJ"))

        For Each icell In rng1
        Select Case icell.Column
            Case Is = 9
                If CheckBox33.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 12
                If CheckBox32.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 15
             If CheckBox31.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 18
             If CheckBox30.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 21
             If CheckBox29.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 24
             If CheckBox28.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 27
             If CheckBox37.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 30
             If CheckBox36.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 33
             If CheckBox35.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
            Case Is = 36
                If CheckBox34.Value = True Then
                Set siterng = Range(icell, icell.Offset(0, -2))
                siterng.EntireColumn.Hidden = False
                End If
        End Select
    Next icell
     
      
    Application.ScreenUpdating = True
End Sub

Any help is appreciated

Any chance you could post some sample of your workbook? It would make it significantly easier to assist

Originally Posted by BigBas

Any chance you could post some sample of your workbook? It would make it significantly easier to assist

I ended up just figuring it out now as i was making a test book to post here. I do need some help on a similar idea though

Along with what i am trying to do here, i also have 3 checkboxes labeled "Complete", "In Process" and "Planned". Each one of these "cities" will have a heading underneath it in the same columns i have in the Union listed above (I, L, O, R, etc). I need a piece of code that can look through all the categories in Column A and filter what the status is on each city. So if i choose Buffalo and NYC in the userform and then select "Complete" in the userform, it should only show categories that are Complete in both those cities while hiding every other city and category.

attached example workbook.

To the best of my knowledge, your second request would not work. You can't filter individual ranges separately. So for example, if you are filtering by COMPLETE tasks, and New York has row 4 INCOMPLETE (which will hide it), but Buffalo shows Row 4 as Complete, there will be conflict. It gets tricky when trying to apply separate filters to separate ranges.

Also, if you are interested, I think you can tidy up your code by quite a bit if you use a listbox vs. multiple checkboxes. I've attached an example of what I mean. Click on Button 6 to show the form, and take a look at the code. The benefits, (1) much easier to follow/read code, (2) scalability; you can add additional cities, and as long as they follow the logic being used (3 columns per city), they will autopopulate into the form and follow the rules of the code, with needing to add a new checkbox and code for it.

Originally Posted by BigBas

To the best of my knowledge, your second request would not work. You can't filter individual ranges separately. So for example, if you are filtering by COMPLETE tasks, and New York has row 4 INCOMPLETE (which will hide it), but Buffalo shows Row 4 as Complete, there will be conflict. It gets tricky when trying to apply separate filters to separate ranges.

Also, if you are interested, I think you can tidy up your code by quite a bit if you use a listbox vs. multiple checkboxes. I've attached an example of what I mean. Click on Button 6 to show the form, and take a look at the code. The benefits, (1) much easier to follow/read code, (2) scalability; you can add additional cities, and as long as they follow the logic being used (3 columns per city), they will autopopulate into the form and follow the rules of the code, with needing to add a new checkbox and code for it.

I'll definitely take a look at this when i get home.

Another though - The first cell for each city is going to be an "Applicable to site?" Y/N answer. Just like with the last cell (In Process, Complete and Planned). Would there be an easy way to add in some code to do the following?

1. Search for a category
2. Select a Y or N checkbox to see the cities that are applicable to that category. All it would be doing is sorting the category (which i have the code for below, and hiding the cities that have a N in the first box. (The form auto fills Y or N in when i input a category)


Code for Category sorting:

sub button_click()
Dim celretro As Range, rng As Range

 Range("A:A").EntireRow.Hidden = False
    Application.ScreenUpdating = False
     
    Set rng = Range("A4", Range("A65536").End(xlUp))
     
    For Each celretro In rng
         
        If Not celretro.Value = ComboBox3.Value Then
             
        celretro.EntireRow.Hidden = True
        
        End If
        
         
    Next celretro
     
     If ComboBox3.Value = "" Then
     Range("A:A").EntireRow.Hidden = False
     End If
end sub

Per the PM request

Works great!

Thanks again!

No problem!