Filter rows containing specific text

[adam]

Hi anyone,

I'm trying to figure out a code that would filter the sheet "Orders" depending on the value in column "M".

Lets say for example when I click the button the code would filter all the rows containing "apple".

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

Why don't you use AutoFilter? No code needed, and anyone can use AutoFilter.

[adam]

I do respect your suggestion Hans. But my intention is to filter the rows that contain the text "apple" in column "N" when the button is clicked.

[HansV]

Where does the user enter the word "apple"?

[Don Wells]

Hi anyone,

I'm trying to figure out a code that would filter the sheet "Orders" depending on the value in column "M".

Lets say for example when I click the button the code would filter all the rows containing "apple".

Any help on this would be kindly appreciated.

Thanks in advance.

The following code should be self explanatory.

Code: Select all

Sub Filter4Apple()
    Range("M1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=113, _
      Criteria1:="=*apple*", Operator:=xlAnd
End Sub
Sub RemoveFilter()
    Range("M1").Select
    Selection.AutoFilter
End Sub

Code modified to filter on M

[Don Wells]

Hi anyone,

I'm trying to figure out a code that would filter the sheet "Orders" depending on the value in column "M".

Lets say for example when I click the button the code would filter all the rows containing "apple".

Any help on this would be kindly appreciated.

Thanks in advance.

The following code should be self explanatory.

Code: Select all

Sub Filter4Apple()
    Range("M1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=113, _
      Criteria1:="=*apple*", Operator:=xlAnd
End Sub
Sub RemoveFilter()
    Range("M1").Select
    Selection.AutoFilter
End Sub

Code modified to filter on M

Oops! That line

Code: Select all

    Selection.AutoFilter Field:=113, _
      Criteria1:="=*apple*", Operator:=xlAnd

should have read

Code: Select all

    Selection.AutoFilter Field:=13, _
      Criteria1:="=*apple*", Operator:=xlAnd

    13 not 113
Sorry

[adam]

The code does not seem to filter?

Code: Select all

Sub Filter4Apple()
    Range("N11").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=14, _
      Criteria1:="=*Expired*", Operator:=xlAnd
End Sub

My headers are on 10. Why is this? I have 11 columns from D to N.

[Don Wells]

The code does not seem to filter?
My headers are on 10. Why is this? I have 11 columns from D to N.

Try this:

Code: Select all

    Sub Filter4Apple()
        Range("D10:N10").AutoFilter
        Selection.AutoFilter Field:=11, _
          Criteria1:="=*Expired*", Operator:=xlAnd
    End Sub

[adam]

Yeah it does help. Instead of using another button to unfilter, how could the line to remove the filter be used so that when the user first clicks the button if the sheet is not filtered, it gets filtered and when the user clicks the button again the sheet gets unfiltered?

[adam]

By the way the when the filter is applied to the sheet the code causes debug message. The code works if filter is not applied to the sheet. How could it be made to work by having the filter applied?

[Don Wells]

The following toggling code should suffice:

Code: Select all

    Sub Filter4Apple()
      If Not ActiveSheet.AutoFilterMode Then
        Range("D10:N10").AutoFilter
        Selection.AutoFilter Field:=11, _
          Criteria1:="=*Expired*", Operator:=xlAnd
      Else
        Range("D10:N10").AutoFilter
      End If
    End Sub

[adam]

Thanks for the help Don. It works fine now and I do really appreciate your help.