Good Afternoon,
I'm having trouble with autofiltering via VBA. Simply, I'm trying to drive the criteria from worksheet ('UserForm') which is separate from the data I'd like to filter ('Data'). It's working ok if each of the six criteria fields have a specific criterion but sometimes I'd like the criteria to be 'All' - how do i do this?
Here is the code i'm using? Any help much appreciated.
sandia01
Sub AutoFilter()
'check for filter, turn on if none exists
Sheets("Data").Activate
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
Sheets("Data").Select
Selection.AutoFilter Field:=1, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J2").Value, Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J3").Value, Operator:=xlAnd
Selection.AutoFilter Field:=3, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J4").Value, Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J5").Value, Operator:=xlAnd
Selection.AutoFilter Field:=5, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J6").Value, Operator:=xlAnd
Selection.AutoFilter Field:=6, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J7").Value, Operator:=xlAnd
If NActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
End Sub
I'm having trouble with autofiltering via VBA. Simply, I'm trying to drive the criteria from worksheet ('UserForm') which is separate from the data I'd like to filter ('Data'). It's working ok if each of the six criteria fields have a specific criterion but sometimes I'd like the criteria to be 'All' - how do i do this?
Here is the code i'm using? Any help much appreciated.
sandia01
Sub AutoFilter()
'check for filter, turn on if none exists
Sheets("Data").Activate
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
Sheets("Data").Select
Selection.AutoFilter Field:=1, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J2").Value, Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J3").Value, Operator:=xlAnd
Selection.AutoFilter Field:=3, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J4").Value, Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J5").Value, Operator:=xlAnd
Selection.AutoFilter Field:=5, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J6").Value, Operator:=xlAnd
Selection.AutoFilter Field:=6, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J7").Value, Operator:=xlAnd
If NActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
End Sub