Results 1 to 12 of 12

Thread: [RESOLVED] Excel VBA: Autofilter with 3 or more criteria and wildcards

  1. #1

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Resolved [RESOLVED] Excel VBA: Autofilter with 3 or more criteria and wildcards

    I have looked through my books, searched the web and spent the last 5 hours swearing , and I'm still no further along with this problem then when I started.

    What I need to do.

    1. Using Autofilter, filter a sheet where the filter consists of three names (for now) that can be in any position in the cell.
    * This worked when I was filtering on two names.

    Now, however the client needs to expand to three (with the expectation that there will be more names added to the filter in the future) and the macro no longer works.

    2. Once filtered the visible rows will then be copied to another worksheet. (This still works)

    Any suggestions?

    The code that is not working is:

    Code:
        Sheets("StartPoint").Select
        Range("A1").Select
        With ActiveSheet
            .AutoFilterMode = False
            .UsedRange
            'Determine last row
            lLastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
            'Set Rng to the A column data rows
            Set Rng = Range("A1", Cells(lLastrow, "K"))
            'Filter the B column to show only the data to be copied
            Rng.AutoFilter field:=2, Criteria1:=Array("=*Harsch*", "=*Cottner*", "=*Markham*"), Operator:=xlFilterValues
            Rng.AutoFilter field:=7, Criteria1:="POS", Operator:=xlFilterValues
            'Copy the visible cells
            Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy
        End With
        Sheets("NewPoint").Select
    You all have helped me in the past, here's hoping you can help me with this one.

    TIA

    datapard
    If you have to do it more than once...
    Automate it!

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    What do you mean by this:

    the filter consists of three names (for now) that can be in any position in the cell.
    Is it the two filter fields that are causing the problem, or the three names in the criteria array?

  3. #3

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    The names in the criteria array. The second filter, filters on the results of the first. The only change to the macro was going to the criteria array for the multiple names with the wildcards. That's what's not working.
    If you have to do it more than once...
    Automate it!

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Got this:

    Code:
    Sub Testo()Dim ar As Variant 
        Dim i As Integer 
         
        ar = Array("=FG1F*", "=FG2F*", "=FG1E0??") 
         
        For i = 0 To UBound(ar) 
            Range("A1:B359").AutoFilter 1, ar(i) 
        Next 
         
    End Sub
    from here:

    http://www.ozgrid.com/forum/showthread.php?t=172620

    If the number goes higher than 3, the loop should still handle it.

  5. #5

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Quote Originally Posted by vbfbryce View Post
    Got this:

    Code:
    Sub Testo()Dim ar As Variant 
        Dim i As Integer 
         
        ar = Array("=FG1F*", "=FG2F*", "=FG1E0??") 
         
        For i = 0 To UBound(ar) 
            Range("A1:B359").AutoFilter 1, ar(i) 
        Next 
         
    End Sub
    from here:

    http://www.ozgrid.com/forum/showthread.php?t=172620

    If the number goes higher than 3, the loop should still handle it.
    Not sure why, as it looks like it should work, but when I incorporated the code into the macro and tried to run it I get "Run-Time Error '1004' Method 'Range of Object' failed.

    This is the changed code:

    Code:
        Range("A1").Select
    
        Dim ar As Variant
        Dim a As Integer
         
        With ActiveSheet
            .AutoFilterMode = False
            .UsedRange
            'Determine last row
            lLastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
            'Set Rng to the A column data rows
            Set Rng = Range("A1", Cells(lLastrow, "K"))
            ar = Array("=*Harsch*", "=*Cottner*", "=*Markham*")
         
            For a = 0 To UBound(ar)
                Range(Rng).AutoFilter 1, ar(a) 'THIS IS WHERE IT IS FAILING
            Next
            'Filter the B column to show only the data to be copied
            Rng.AutoFilter field:=2, Criteria1:=Array("=*Harsch*", "=*Cottner*", "=*Markham*"), Operator:=xlFilterValues
            Rng.AutoFilter field:=7, Criteria1:="POS", Operator:=xlFilterValues
            'Copy the visible cells
            Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy
    '        Rng.AutoFilter
    '        .UsedRange 'Reset the last cell
        End With
    Can anyone see what I've done wrong here? I desperately need to get this to work. I'm at a complete halt on this project until I can get past this point.

    Thank you all for any help you can give me.

    +++Additional note+++
    I just realized that I had only tagged this for MS Office 2003. It needs to be able to run in both 2003 and 2010, which it was doing until I changed this section of the macro.


    datapard
    Last edited by datapard; Aug 20th, 2014 at 04:42 AM. Reason: Additional information that was missed.
    If you have to do it more than once...
    Automate it!

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    which line is highlighted when you get that error?

    EDIT: Just noticed you already showed that, sorry!

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Try dimming "rng" as Range, seemed to fix it for me.

  8. #8

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Quote Originally Posted by vbfbryce View Post
    Try dimming "rng" as Range, seemed to fix it for me.
    Just tried this, unfortunately, it still fails at the same point with the same error message.
    If you have to do it more than once...
    Automate it!

  9. #9
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Which version of Excel you currently trying it in? Can you zip and attach the actual workbook, with code?

  10. #10

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Quote Originally Posted by vbfbryce View Post
    Which version of Excel you currently trying it in? Can you zip and attach the actual workbook, with code?
    Currently I'm working in Excel 2010. I had started this project in Excel 2003. Unfortunately I cannot attach the actual workbook as I am working under an NDA. I can show the code as long as I change the names being used in the criteria.
    If you have to do it more than once...
    Automate it!

  11. #11
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Looks like you can't have more than two criteria (at least with wildcards), from what I can tell. Here is an ugly workaround to filter with 3 or more. It first adds a formula in column L (my data only went to column K), then filters based on that column, then clears it. It does leave the filter symbol in row 1 in column L, which doesn't look good...

    Code:
    Sub aFilter()
        Dim ws As Worksheet
        Dim lr As Long
        Dim j As Long
        Dim crit(2) As String
        Dim strIf As String
        
        Set ws = ActiveSheet
        lr = ws.Range("a" & Rows.Count).End(xlUp).Row
        
        crit(0) = "*ob*"
        crit(1) = "*am*"
        crit(2) = "*vi*"
        
        With ws
        .AutoFilterMode = False
        .Range("l1").Value = "Temp"
        For j = 2 To lr
            strIf = "=if(or(isnumber(search(" & Chr(34) & crit(0) & Chr(34) & ",a" & j & ")),isnumber(search(" & Chr(34) & crit(1) & Chr(34) _
                & ",a" & j & ")),isnumber(search(" & Chr(34) & crit(2) & Chr(34) & ",a" & j & "))),999,0)"
            .Range("l" & j).Formula = strIf
        Next j
    
        End With
        ws.Range("a1:l" & lr).AutoFilter field:=12, Criteria1:="999", Operator:=xlFilterValues
        ws.Range("l1").EntireColumn.ClearContents
        Set ws = Nothing
    End Sub

  12. #12

    Thread Starter
    Lively Member datapard's Avatar
    Join Date
    May 2008
    Location
    Silicon Valley, CA
    Posts
    107

    Re: Excel VBA: Autofilter with 3 or more criteria and wildcards

    Quote Originally Posted by vbfbryce View Post
    Looks like you can't have more than two criteria (at least with wildcards), from what I can tell. Here is an ugly workaround to filter with 3 or more. It first adds a formula in column L (my data only went to column K), then filters based on that column, then clears it. It does leave the filter symbol in row 1 in column L, which doesn't look good...
    I had pretty much come to that conclusion myself. You can have as many criteria in an autofilter criteria array as you need...as long as none of them include a wildcard. So I used a most inelegant and inefficient (at least to me) method to give me what I needed for my project. I will be looking closer at your solution to the problem and see if it might work better.

    In any case, that you for trying to help.

    datawolf
    If you have to do it more than once...
    Automate it!

Posting Permissions

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



Click Here to Expand Forum to Full Width