Private Sub cmdsave_Click()
Dim myFilter() As Variant
Dim c As Range
ReDim myFilter(0)
Dim ws As Worksheet
Dim LR As Long
Dim rng As Range, cel As Range
Set ws = ActiveSheet
With ws
.AutoFilterMode = False
LR = .Range("B" & .Rows.Count).End(xlUp).Row
Set rng = Range("E7:E" & LR)
End With
For i = 1 To 22
If Me.Controls("CheckBox" & i).Value = True Then
Set c = Range("E:E").Find(Me.Controls("Checkbox" & i).Caption, , xlValues, xlPart, xlByRows, xlNext, False)
If Not c Is Nothing Then
myFilter(UBound(myFilter)) = Me.Controls("Checkbox" & i).Caption
ReDim Preserve myFilter(UBound(myFilter) + 1)
End If
End If
Next i
If IsError(Application.Match("*", (myFilter), 0)) Then
MsgBox "Either there are no Records for the Item Selected " & vbCrLf & _
"or you didn't select an Item...Please Select an item"
Exit Sub
Else
ReDim Preserve myFilter(UBound(myFilter) - 1)
End If
Application.ScreenUpdating = False
ws.Rows(8 & ":" & LR).EntireRow.Hidden = True
For i = LBound(myFilter) To UBound(myFilter)
For Each cel In rng
If InStr(Trim(cel.Value), myFilter(i)) > 0 Then
If cel.EntireRow.Hidden = True Then
cel.EntireRow.Hidden = False
End If
End If
Next cel
Next i
Application.ScreenUpdating = True
End Sub
Bookmarks