Hide rows based on userform checkbox

Question

Good morning everyone,

I have a macro that I want to sort data. A button in my workbook calls a small userform with 10 checkboxes. The user should pick those categories that he wants to review and click sort. The result I want is for only the categories he chose to be displayed but I am getting an all or nothing result out of the attached macro. Below is that macro that supports the form/button to sort the categories. I have searched through Google and several other forums and can't find an answer relevant to my problem! Any help you could offer would be greatly appreciated.

Thanks!

Private Sub cmdSort_Click()

LastRow = Range("A" & Rows.Count).End(xlUp).Row

If chkFE = True Then
    For Each cell In Range("BC4:BC" & LastRow)
        If UCase(cell.Value) <> "Fire Extinguishers" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkChem = True Then
    For Each cell In Range("BD4:BD" & LastRow)
        If UCase(cell.Value) <> "Chem" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkFL = True Then
    For Each cell In Range("BE4:BE" & LastRow)
        If UCase(cell.Value) <> "FL" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkElec = True Then
    For Each cell In Range("BF4:BF" & LastRow)
        If UCase(cell.Value) <> "Elec" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkFP = True Then
    For Each cell In Range("BG4:BG" & LastRow)
        If UCase(cell.Value) <> "FP" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkLift = True Then
    For Each cell In Range("BH4:BH" & LastRow)
        If UCase(cell.Value) <> "Lift" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkPPE = True Then
    For Each cell In Range("BI4:BI" & LastRow)
        If UCase(cell.Value) <> "PPE" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkPS = True Then
    For Each cell In Range("BJ4:BJ" & LastRow)
        If UCase(cell.Value) <> "PS" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkSTF = True Then
    For Each cell In Range("BK4:BK" & LastRow)
        If UCase(cell.Value) <> "STF" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If

If chkErgonomics = True Then
    For Each cell In Range("BL4:BL" & LastRow)
        If UCase(cell.Value) <> "Ergonomics" Then
        cell.EntireRow.Hidden = True
        End If
    Next

End If



Unload frmSort

End Sub

Answer 1

I wanted to share the solution that @AlphaFrog provided me with, it works perfectly:

Private Sub cmdSort_Click() 

Dim i As Long, rng As Range, arrCriteria As Variant 

Set rng = Rows(3) 'Headers
arrCriteria = Array("Fire Extinguishers", "Chem", "FL", "Elec", "FP", _ 
"Lift", "PPE", "PS", "STF", "Ergonomics") 

Application.ScreenUpdating = False 
Rows.Hidden = False 
With Range("BC3:BL" & Range("A" & Rows.Count).End(xlUp).Row) 
    For i = 1 To 10 
        If Me.Controls("CheckBox" & i) Then 
            .AutoFilter i, arrCriteria(i - 1) 
            Set rng = Union(rng, .SpecialCells(xlCellTypeVisible).EntireRow) 
            .AutoFilter 
        End If 
    Next i 
    .Parent.AutoFilterMode = False 
    .EntireRow.Hidden = True 
    rng.EntireRow.Hidden = False 
End With 
Application.ScreenUpdating = True 

Unload frmSort 

End Sub

Link to the original answer: http://www.ozgrid.com/forum/showthread.php?t=175539

Answer 2

You are actually filtering, not sorting. Which raises the question, why not just let the user the Excel's Filter button and dialog?

To answer your question, your code will only ever work if one Checkbox is checked. For every Checkbox that's checked your code is hiding the rows for all other categories. So only the category for the last Checkbox will have rows showing

You could try reversing your logic. Start with all rows hidden, and set Hidden = False for any rows whose category is clicked.