VBA Code to AutoFilter and hide rows based on criteria

[MWeber2222]

Hi,

I am working on a VBA project. One of the steps in the code should be to hide all rows that contain "Xbox", and also to hide all blank rows. I would like to use AutoFilter for this. I have attached an example of the file. The actual data contains 1,000 rows. I would appreciate any help that may be offered.

Thanks!
MW

 

[Luke M]

Hi MW,

Pretty straight-forward.

Sub FilterMacro()
'Where is filter applied?
With Range("A13").CurrentRegion
    .AutoFilter Field:=6, Criteria1:="<>*xbox*" _
        , Operator:=xlAnd, Criteria2:="<>"
End With
End Sub

By using current region, the code says to grab the block of data/cells that A13 is a part of. I was assuming row 13 was the header row.

 

[MWeber2222]

Luke,

Thanks for the help.

I guess I was defining the range improperly, and forgetting the *...* around Xbox. Yours worked great (just had to adjust field from 6 to 5.

This is by far the most helpful and responsive Excel forum I have tried. You guys rock!

Hi MW,

Pretty straight-forward.

Sub FilterMacro()
'Where is filter applied?
With Range("A13").CurrentRegion
    .AutoFilter Field:=6, Criteria1:="<>*xbox*" _
        , Operator:=xlAnd, Criteria2:="<>"
End With
End Sub

By using current region, the code says to grab the block of data/cells that A13 is a part of. I was assuming row 13 was the header row.

 

[Luke M]

Glad to help! Yep, the wildcards make the different between statements "not equal to" and "does not contain". Have a good one!

 

[MWeber2222]

Glad to help! Yep, the wildcards make the different between statements "not equal to" and "does not contain". Have a good one!

Luke,

The code you helped me with previously worked great. Now however there is another wrench in the machine. Besides the autofilter in the original example, the folks I am developing this for want to add a third autofilter criteria in a different column, so they want rows to be hidden if:

1. They contain "xbox"
2. They are blank
3. They contain "supplies only" in column 13

I have included the current code for the two criteria, and a sample file with the new columns added.

    'Hide blank rows / xbox rows
    With Range("A13").CurrentRegion
    .AutoFilter Field:=5, Criteria1:="<>*xbox*" _
        , Operator:=xlAnd, Criteria2:="<>"
        End With

You were quite helpful last time. I hope you will be able to help again.

Thanks!

MW

 

[Luke M]

In your sample file, why is there a big gap between column L and R? Assuming that col R, which has the info in question is not actually separated by a gap in the real file, code would be something like

Sub FilterMacro()
'Where is filter applied?
With Range("A13").CurrentRegion
    .AutoFilter field:=6, Criteria1:="<>*xbox*" _
        , Operator:=xlAnd, Criteria2:="<>"
    .AutoFilter field:=13, Criteria1:="<>supplies only"
End With
End Sub

As you can see, just add another filter line saying which column and which criteria you want.

 

[MWeber2222]

Luke,

Thanks. I was definitely thinking too much into this. Once again, I truly appreciate your help!

Regards,
MW

 

[Lizzz]

Hello,

I am really new using and creating macros, I have a similar problem. But the data I want to filter come from another sheet, so I have been using "Option Explicit" and the Autofilter I wanto to use is below an "If". I want to hide all the rows that contain "Daimler". But do not know how to declare correctly all the variables.

I really appreciate the help.

 

[Monty]

Lizzz

Welcome to Chandoo Forum!

Try this!

Let me any challenges...Happy to help you.

 

[Lizzz]

It looks so neat! Thank you, I will try it out.
I noticed it hides "Daimler" in both worksheets, but I would like to hide just the row of the worsheet with the bottons.

Thank you, you're great.

 

[Lizzz]

Forget it, it is PERFECT! :D

 

[Monty]

Lizzz

Happy that code is helpful..

Please let me know any further challenges...Happy to help you!

 

[Lizzz]

Actually, I would like to ask for your advice
The filter wich you have helped me with is linked to the "Material n" boxes.
And for example, if I check "Material 1", the range for "Material 1" will be displayed and the other ranges for the other Materials will be hidden, plus it will hide the ones that contain "Daimler" in the Material 1 range, showing just "US".
I would also like to add two extra filters, or "subfilters" so I can have the option of hiding and showing the tests with New Material or with HYGR. Should I just have one box "New Material" and one "HYGR" for all the Materials. Or would it be easier if I create one for each Material box I have?

Have a great week!