Solved VBA Filter Multiple Criteria Options

August 23, 2019 at 09:32:29
Specs: Windows
I need a VBA code to filter data based on the text found in column D. I want to add this macro to a button that moves me to a filtered sheet.

I want the code to show 2 criteria. I do NOT want both criteria to be met, instead I want to show rows where either of the two criteria is met.

Ex. Show rows with both 'Example A' OR 'Example B' in the 4th column. Hide every other row.

Additionally, what VBA would I use to remove the filter after I activate a different sheet? The removal would have to show all data after and not leave the other rows hidden. Either when another sheet is activated, or when the filtered sheet is "deactivated' would work just fine.

Thank you in advance for any assistance.


See More: VBA Filter Multiple Criteria Options


✔ Best Answer
September 30, 2019 at 12:10:16
This seems to work...

'Apply Filter
    Worksheets(2).Range("D:D").AutoFilter Field:=1, _
        Criteria1:=Array("Example A", "Example B", "Example C"), Operator:=xlFilterValues

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
August 24, 2019 at 19:37:16
What does this mean:

"I want to add this macro to a button that moves me to a filtered sheet."

I don't have access to Excel for a couple of days so all I can do is toss out a couple of concepts.

To hide the rows, basically what you need to do is use a For-Next loop to check and see If the value in Column D does NOT = A AND does NOT = B. If the AND is True, Then EntireRow.Hidden = True.

For the Unhide portion of your question, try this in the sheet module:

Private Sub Worksheet_Deactivate()
   Me.Rows.EntireRow.Hidden = False
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
August 26, 2019 at 06:30:54

Sub MyFilter()

Dim lastRw As Long, rw As Long

'Determine last Row with data in Column D
   lastRw = Cells(Rows.Count, 4).End(xlUp).Row
 
'Hide Rows that meet criteria
   For rw = 1 To lastRw
      If Cells(rw, 4) <> "Example A" And Cells(rw, 4) <> "Example B" Then
         Rows(rw).EntireRow.Hidden = True
      End If
   Next

End Sub

Private Sub Worksheet_Deactivate()

'Unhide all Rows when Deactivating sheet
   Me.Rows.EntireRow.Hidden = False

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#3
August 26, 2019 at 13:46:22
I didn't think to achieve this is another method rather than using a filter. I'll try to elaborate on what I meant about the button:

I have 2 sheets in question here. Sheet 1 has a shape that I want to assign a macro to and make it a button. That will jump to sheet 2 with the applied filter.

Sheet 2 is accessible otherwise without the filter. The "button" just helps me find specific data.

I am not at my desk at the moment to test your code (I will later this week), but upon quick glance, doesn't that code hide a row if "Example A" or "Example B" are found? I want the opposite of that; show only Example A or Example B and hide everything else. I'll test this to be sure.

Thank you as usual for your assistance.

message edited by Steven4321


Report •

Related Solutions

#4
August 26, 2019 at 18:58:14
If Cells(rw, 4) <> "Example A" And Cells(rw, 4) <> "Example B" Then
         Rows(rw).EntireRow.Hidden = True

If the Cell is Not equal to "Example A" AND it's Not equal to "Example B" then it must be equal to something else. Since the Cell equals something else, hide it.

message edited by DerbyDad03


Report •

#5
August 28, 2019 at 08:58:00
Works perfectly. Thank you very much.

Report •

#6
August 28, 2019 at 09:50:32
I take it all back. This is much faster and more efficient. Sorry. I rushed through the other code while traveling. I should have been more focused.

Sub MyFilter_V1()

'Apply Filter
    Worksheets(1).Range("D:D").AutoFilter Field:=1, _
        Criteria1:="Example A", Operator:=xlOr, Criteria2:="Example B"
        
End Sub

Private Sub Worksheet_Deactivate()

'Toggle Filter Off
  If Worksheets(1).AutoFilterMode Then _
     Worksheets(1).AutoFilterMode = False
     
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#7
August 28, 2019 at 10:20:34
I'm sure I am doing something wrong here, but that code makes the first page (with the "button") shrink to a single row. I want the code to do stuff on sheet 2 when I click the "button" on sheet 1.

Any ideas?


Report •

#8
August 28, 2019 at 10:49:25
Which code are you referring to? My updated AutoFilter code or the For-Next Loop code in Response #2?

In either case, the code has to reference the sheet that you want it to act on. Note that I now use Worksheets(2) in the following code.

In future, if you include those types of details in your original question, we can be more targeted with our response. Without the details, we either have to guess (always dangerous) or provide a generic solution and leave it to you to customize it to meet your needs.


Sub MyFilter()

'Apply Filter
    Worksheets(2).Range("D:D").AutoFilter Field:=1, _
        Criteria1:="Example A", Operator:=xlOr, Criteria2:="Example B"
        
End Sub

Private Sub Worksheet_Deactivate()

'Toggle Filter Off
  If Worksheets(2).AutoFilterMode Then _
     Worksheets(2).AutoFilterMode = False
     
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
August 28, 2019 at 11:02:57
I was referencing the updated AutoFilter. As I assumed, I was just moving too quick and missed that part that noted worksheet 1. The code works correctly once I change the worksheet.

That said, the deactivation does not work. The sheet remains filtered.


Report •

#10
August 28, 2019 at 11:37:57
It works fine for me.

You do have the Deactivate code stored in the Sheet 2 Module don't you?

You want it to remove the filter when Sheet 2 is Deactivated, so it should be stored in that Sheet's Module.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#11
September 18, 2019 at 10:05:30
I took another look at this sheet and realized the issue. I had forgotten to change the deactivate sheet as well from 'worksheet(1)'.

Stupidity happens when you rush through things.

Thank you for the help.


Report •

#12
September 27, 2019 at 13:28:23
If I need to add a 3rd criteria, the code you provided no longer works. Google tells me for more than 2 criteria, I need to use the Array code but that too isn't working. Here is what I currently have. Goal of adding a third criteria.

'Apply Filter
    Worksheets(2).Range("D:D").AutoFilter Field:=1, _
        Criteria1:="Example A", Operator:=xlOr, Criteria2:="Example B", Operator:=xlOr, Criteria3:="Example C"


Report •

#13
September 27, 2019 at 16:59:16
re: "Google tells me for more than 2 criteria, I need to use the Array code but that too isn't working."

A guy calls his mechanic. "Hey, my car isn't working. What's wrong with it?"

We don't know what "array code" you used and we don't know what "isn't working" means.

How about showing us what array code you tried so we can try and figure out what's wrong with it?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#14
September 30, 2019 at 12:10:16
✔ Best Answer
This seems to work...

'Apply Filter
    Worksheets(2).Range("D:D").AutoFilter Field:=1, _
        Criteria1:=Array("Example A", "Example B", "Example C"), Operator:=xlFilterValues

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#15
September 30, 2019 at 13:11:43
I always think I'm giving you enough information and you always find where I'm lacking. Once again I tip my hat to you.

This is the original code I tried.

Worksheets(2).Range("D:D").AutoFilter Field:=1, Criteria1:=Array( _
                    "Example A", _
                    "Example B", _
                    "Example C", _
                    Operator:=xlFilterValues)

Your code works exactly as intended. Thank you very much.


Report •

#16
September 30, 2019 at 17:05:41
As you can see, the Array should only contain the Criteria. If you add the Operator argument inside the parenthesis, VBA is going to try to use it as a 4th Criteria

Of course the syntax is wrong for a Criteria argument, so VBA isn't going to like it at all.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#17
October 4, 2019 at 12:52:20
Yes that makes sense now that I see proper code.

Report •

Ask Question