Autofilter

**rachmul18** · 09-08-2010, 01:43 PM

Hi!

I have a sheet with 6 columns that can be filtered upon. I want coding to say the following:

If cell a1 =all then don't filter then if cell a1 <> "all" filter on that criteria else
if cell a2=all then dont filter then if cell b1 <>"all" filter on that criteria.

Note: Cells A1:A6, either are all "All" or only have one criteria.

This is my current code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Sheet6
        .AutoFilterMode = False
            With .Range("A5:AP5")
                .AutoFilter
                .AutoFilter Field:=39, Criteria1:=Range("A4").Text
                .AutoFilter Field:=42, Criteria1:=Range("B4").Text
                .AutoFilter Field:=41, Criteria1:=Range("C4").Text
                .AutoFilter Field:=40, Criteria1:=Range("D4").Text
                .AutoFilter Field:=38, Criteria1:=Range("E4").Text
                .AutoFilter Field:=36, Criteria1:=Range("F4").Text
             End With
End With
        
End Sub

However, it filters all those criterion instead of just one.

Thanks in advance!

**pike** · 09-09-2010, 07:11 AM

Hi rachmul18
Welcome to the forum
maybe something like...

  

  if  Range("A4").Text  <>"all" then    
 .AutoFilter Field:=39, Criteria1:=Range("A4").Text
   elseif Range("B4").Text  <>"all" then   
 .AutoFilter Field:=42, Criteria1:=Range("B4").Text
      elseif Range("C4").Text  <>"all" then   
    .AutoFilter Field:=41, Criteria1:=Range("C4").Text
               elseif Range("...

**rachmul18** · 09-09-2010, 09:16 AM

Pike,

Thanks for the welcome and help! I tried the code you suggested and I think it will work but now its not filtering at all.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Sheet6
.AutoFilterMode = False
            With .Range("A5:AP5")
        If Range("A4").Text <> "All" Then
    .AutoFilter Field:=39, Criteria1:=Range("A4").Text
        ElseIf Range("B4").Text <> "All" Then
    .AutoFilter Field:=42, Criteria1:=Range("B4").Text
    End If
End With
End With
End Sub

Do you have any suggestions on why its running without error but not coming up with any results? I need the code to automatically run based on data selected on a different sheet.

Thanks in advance.

**romperstomper** · 09-09-2010, 09:28 AM

You actually want them as separate conditions, not ElseIfs:

With Sheet6
.AutoFilterMode = False
      With .Range("A5:AP5")
         If .Range("A4").Value <> "All" Then 
             .AutoFilter Field:=39, Criteria1:=Range("A4").Value
         Else
             .AutoFilter Field:=39
         End If
         If .Range("B4").Value <> "All" Then 
             .AutoFilter Field:=42, Criteria1:=Range("B4").Value
         Else
             .AutoFilter Field:=42
         End If
' and so on
End With
End With

Note: if you arranged your criteria cells in the same order as your fields, you could use a loop to simplify things.

**rachmul18** · 09-09-2010, 09:48 AM

Thanks for your help. I tried the code and am getting an error "Autofilter method of Range class failed". When I debug, it higlights ".AutoFilter Field:=42, Criteria1:=Range("B4").Value
"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Sheet6
.AutoFilterMode = False
            With .Range("A5:AP5")
        If .Range("A4").Value <> "All" Then
            .AutoFilter Field:=39, Criteria1:=Range("A4").Value
        Else
            .AutoFilter Field:=39
        End If
        If .Range("B4").Value <> "All" Then
            .AutoFilter Field:=42, Criteria1:=Range("B4").Value
        Else
            .AutoFilter Field:=42
        End If
End With
End With
End Sub

Also, before that error - it autofiltered for "All" since the procedure produces a custom filter.

Any suggestions? Thanks!

**romperstomper** · 09-09-2010, 09:58 AM

Sorry - didn't inspect your code closely enough - try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngData As Range
    With Sheet6
        .AutoFilterMode = False
        Set rngData = .Range("A5:AP5")
            If .Range("A4").Value <> "All" Then rngData.AutoFilter Field:=39, Criteria1:=.Range("A4").Value
            If .Range("B4").Value <> "All" Then rngData.AutoFilter Field:=42, Criteria1:=.Range("B4").Value
        End With
    End With
End Sub

**rachmul18** · 09-09-2010, 10:21 AM

Romperstomper,

I'm getting the same error as earlier. While debugging, it highlights Then RngData.Autofilter:=42 etc. Hmmm...

**romperstomper** · 09-09-2010, 10:36 AM

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngData As Range
    With Sheet6
        .AutoFilterMode = False
        Set rngData = .Range("A5:AP5")
        rngData.Autofilter
            If .Range("A4").Value <> "All" Then rngData.AutoFilter Field:=39, Criteria1:=.Range("A4").Value
            If .Range("B4").Value <> "All" Then rngData.AutoFilter Field:=42, Criteria1:=.Range("B4").Value
        End With
    End With
End Sub

If not, I'll need to see the workbook I think.

**rachmul18** · 09-09-2010, 10:48 AM

Rompstomper,

The code works now however the IF A4="All" it custom filters "All" (therefore no results) and I only want one filter to be active at a time. I can send a simplified version of my workbook if that's easier.

Thanks!

**romperstomper** · 09-09-2010, 10:53 AM

What is the exact text in A4? All, ALL, all? any leading or trailing spaces? Might be better off catering for all those:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngData As Range
    With Sheet6
        .AutoFilterMode = False
        Set rngData = .Range("A5:AP5")
        rngData.Autofilter
            If trim(ucase(.Range("A4").Value)) <> "ALL" Then rngData.AutoFilter Field:=39, Criteria1:=.Range("A4").Value
            If trim(ucase(.Range("B4").Value)) <> "ALL" Then rngData.AutoFilter Field:=42, Criteria1:=.Range("B4").Value
        End With
    End With
End Sub

**rachmul18** · 09-09-2010, 11:02 AM

"ALL" is the text string and is only 3 characters long. The problem with the above code is the second autofilter for criteria 42 is custom filtering for "All" and I only want one filter to be in use. For example, If A4 and B4 = "All" then do nothing otherwise If A4 <>All, Filter or If B4<>All then Filter on B4.

Hope that helps. Thanks!

**romperstomper** · 09-09-2010, 11:03 AM

Did you try the last version?

**rachmul18** · 09-09-2010, 11:18 AM

Yes, and it custom filtered for "All" for Field 42.

**romperstomper** · 09-09-2010, 11:26 AM

Then I think I need to see your workbook, if possible. Or a sample that demonstrates the problem.

**rachmul18** · 09-09-2010, 11:51 AM

I created a sample version attached. On sheet A, If I choose an item "Apples" then Cell B4 in sheet B will say "Apples" as well and I want that report to be filtered on "Apples". You cannot select more than 1 criteria. So if apple is chosen, then "1" cannot be chose in tandem.

Let me know if you need more information. Thank you so much for your help!