Filter rows containing specific text
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Filter rows containing specific text
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.
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.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter rows containing specific text
Why don't you use AutoFilter? No code needed, and anyone can use AutoFilter.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter rows containing specific text
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.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter rows containing specific text
Where does the user enter the word "apple"?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Filter rows containing specific text
The following code should be self explanatory.adam wrote: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.
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
Regards
Don
Don
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Filter rows containing specific text
Oops! That lineDon Wells wrote:The following code should be self explanatory.adam wrote: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.Code modified to filter on MCode: 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: Select all
Selection.AutoFilter Field:=113, _
Criteria1:="=*apple*", Operator:=xlAnd
Code: Select all
Selection.AutoFilter Field:=13, _
Criteria1:="=*apple*", Operator:=xlAnd
Sorry
Regards
Don
Don
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter rows containing specific text
The code does not seem to filter?
My headers are on 10. Why is this? I have 11 columns from D to N.
Code: Select all
Sub Filter4Apple()
Range("N11").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, _
Criteria1:="=*Expired*", Operator:=xlAnd
End Sub
Best Regards,
Adam
Adam
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Filter rows containing specific text
Try this:adam wrote:The code does not seem to filter?
My headers are on 10. Why is this? I have 11 columns from D to N.
Code: Select all
Sub Filter4Apple()
Range("D10:N10").AutoFilter
Selection.AutoFilter Field:=11, _
Criteria1:="=*Expired*", Operator:=xlAnd
End Sub
Regards
Don
Don
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter rows containing specific text
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?
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter rows containing specific text
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?
Best Regards,
Adam
Adam
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Filter rows containing specific text
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
Regards
Don
Don
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter rows containing specific text
Thanks for the help Don. It works fine now and I do really appreciate your help.
Best Regards,
Adam
Adam