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

Reply ↓  Report •

✔ Best Answer
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



#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


Reply ↓  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


Reply ↓  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


Reply ↓  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


Reply ↓  Report •

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

Reply ↓  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


Reply ↓  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?


Reply ↓  Report •

#8
August 28, 2019 at 10:49:25
✔ Best Answer
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


Reply ↓  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.


Reply ↓  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


Reply ↓  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.


Reply ↓  Report •

Ask Question