VBA Autofilter (Multiple Criteria)

[sandia01]

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

 

[sandia01]

ahhh. i see now.

you're a genuis son.

Thanks for all your help. You've inspired me to get practicing my VBA.

Kindest Regards

Ats.