Page 1 of 2 12 LastLast
Results 1 to 10 of 11

VBA Autofilter (Multiple Criteria)

This is a discussion on VBA Autofilter (Multiple Criteria) within the Excel Questions forums, part of the Question Forums category; Good Afternoon, I'm having trouble with autofiltering via VBA . Simply, I'm trying to drive the criteria from worksheet ('UserForm') ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    11

    Lightbulb VBA Autofilter (Multiple Criteria)

    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

  2. #2
    Board Regular
    Join Date
    Jun 2011
    Posts
    124

    Default Re: VBA Autofilter (Multiple Criteria)

    use if statements to determine if the cell has a value or not and only apply that particular criteria when a value is present ?

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    11

    Lightbulb Re: VBA Autofilter (Multiple Criteria)

    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

  4. #4
    Board Regular
    Join Date
    Jun 2011
    Posts
    124

    Default Re: VBA Autofilter (Multiple Criteria)

    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

  5. #5
    New Member
    Join Date
    Oct 2011
    Posts
    11

    Lightbulb Re: VBA Autofilter (Multiple Criteria)

    Thanks TiaXL. It's working nicely now. Very grateful.

  6. #6
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,647

    Default Re: VBA Autofilter (Multiple Criteria)

    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.
    Regards,


    Wigi


    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  7. #7
    New Member
    Join Date
    Oct 2011
    Posts
    11

    Default Re: VBA Autofilter (Multiple Criteria)

    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()

    Sheets("Data").Select

    If ActiveWorkbook.Worksheets("UserForm").Range("X7") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X7") <> "All" Then
    Selection.AutoFilter Field:=1, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X7").Value, Operator:=xlAnd
    End If
    If ActiveWorkbook.Worksheets("UserForm").Range("X8") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X8") <> "All" Then
    Selection.AutoFilter Field:=2, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X8").Value, Operator:=xlAnd
    End If
    If ActiveWorkbook.Worksheets("UserForm").Range("X9") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X9") <> "All" Then
    Selection.AutoFilter Field:=3, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X9").Value, Operator:=xlAnd
    End If
    If ActiveWorkbook.Worksheets("UserForm").Range("X10") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X10") <> "All" Then
    Selection.AutoFilter Field:=4, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X10").Value, Operator:=xlAnd
    End If
    If ActiveWorkbook.Worksheets("UserForm").Range("X11") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X11") <> "All" Then
    Selection.AutoFilter Field:=5, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X11").Value, Operator:=xlAnd
    End If
    If ActiveWorkbook.Worksheets("UserForm").Range("X12") <> Empty Or ActiveWorkbook.Worksheets("UserForm").Range("X12") <> "All" Then
    Selection.AutoFilter Field:=6, Criteria1:=ActiveWorkbook.Worksheets("UserForm").Range("X12").Value, Operator:=xlAnd
    End If

    End Sub

  8. #8
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,647

    Default Re: VBA Autofilter (Multiple Criteria)

    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.
    Regards,


    Wigi


    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  9. #9
    New Member
    Join Date
    Oct 2011
    Posts
    11

    Default Re: VBA Autofilter (Multiple Criteria)

    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.

  10. #10
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,647

    Default Re: VBA Autofilter (Multiple Criteria)

    Quote Originally Posted by sandia01 View Post
    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...
    Regards,


    Wigi


    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com