VBA Autofilter (Multiple Criteria)

sandia01

New Member
Joined
Oct 21, 2011
Messages
11
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
use if statements to determine if the cell has a value or not and only apply that particular criteria when a value is present ?
 
Upvote 0
Thank you very much.

I should have explained that I'm a novice to VBA. Do you have an example of how I would begin? Again, any help appreciated.

sandia01
 
Upvote 0
in the same way you have done already just include for each line

if isempty(workbooks("Userform").sheets("Sheet1").range("J2")) = true then

else
Selection.AutoFilter Field:=1, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("J2").Value, Operator:=xlAnd
end if
 
Upvote 0
Hello

You might also want to look at the condensed macro below - can give you additional insights I guess.

Code:
Sub AutoFilter()
'check for filter, turn on if none exists

    Dim i As Integer
    Dim rngBaseCell As Range

    Set rngBaseCell = [UserForm!J1]

    With [Data!A1].CurrentRegion
        .Parent.AutoFilterMode = False
        For i = 1 To 6
            If Len(rngBaseCell.Offset(i).Value) > 0 Then
                .AutoFilter Field:=i, Criteria1:=rngBaseCell.Offset(i).Value
            End If
        Next
    End With
End Sub

Code is untested, so please test it on a copy of your file.
 
Upvote 0
Hello (again),

Thanks for you help and suggetsions. I've now run into some fresh hell with this autofilter vba.

When i select criteria from worksheet 'UserForm' it now filters everything on worksheet 'Data' to zero (so no data). Am I conflicting the IF statements or something? I basically still want it to filter if a criteria is selected and do not filter on a column if 'All' is in the cell.

My full code is as follows:


Sub AutoFilterCriteria()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Sheets("Data").Select<o:p></o:p>
<o:p> </o:p>
If ActiveWorkbook.Worksheets("UserForm").Range("X7") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X7") <> "All" Then<o:p></o:p>
Selection.AutoFilter Field:=1, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X7").Value, Operator:=xlAnd<o:p></o:p>
End If<o:p></o:p>
If ActiveWorkbook.Worksheets("UserForm").Range("X8") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X8") <> "All" Then<o:p></o:p>
Selection.AutoFilter Field:=2, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X8").Value, Operator:=xlAnd<o:p></o:p>
End If<o:p></o:p>
If ActiveWorkbook.Worksheets("UserForm").Range("X9") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X9") <> "All" Then<o:p></o:p>
Selection.AutoFilter Field:=3, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X9").Value, Operator:=xlAnd<o:p></o:p>
End If<o:p></o:p>
If ActiveWorkbook.Worksheets("UserForm").Range("X10") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X10") <> "All" Then<o:p></o:p>
Selection.AutoFilter Field:=4, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X10").Value, Operator:=xlAnd<o:p></o:p>
End If<o:p></o:p>
If ActiveWorkbook.Worksheets("UserForm").Range("X11") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X11") <> "All" Then<o:p></o:p>
Selection.AutoFilter Field:=5, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X11").Value, Operator:=xlAnd<o:p></o:p>
End If<o:p></o:p>
If ActiveWorkbook.Worksheets("UserForm").Range("X12") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X12") <> "All" Then<o:p></o:p>
Selection.AutoFilter Field:=6, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X12").Value, Operator:=xlAnd<o:p></o:p>
End If<o:p></o:p>
<o:p> </o:p>
End Sub<o:p></o:p>
 
Upvote 0
The Or's should be And's.

By the way, did you try my suggestion? If it does not do what you want, please explain what is still missing.
 
Upvote 0
Thanks for your reply.

I've replaced the Or's with And's and it does the same thing (although now it keeps all the data on the worksheet - rather than filtering so you can't see any data as before).

from your earlier suggestion - how would i apply that to multiple criteria e.g. 6 columns of data each with a different criteria for filter?

Kind Regards

Ats.
 
Upvote 0
from your earlier suggestion - how would i apply that to multiple criteria e.g. 6 columns of data each with a different criteria for filter?

That's what the loop over i (from 1 to 6) takes care of...
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top