Multiple Filters in Excel

[kathleen0927]

Hi!

I have an Excel spreadsheet where I have added macros attached to control buttons to filter the 1825 row list based on discipline criteria. See code below for 1 example. I also have a macro that hides rows that have a value in them (to hide rows where the item is closed - "C" in the cell means closed and the code will only allow blanks). My problem is if you chose 1 of the discipline filter first, then select the macro to hide the closed, all rows are hidden. How can I change the "closed" code to filter the list AFTER another filter has been selected?

Sample Discipline Filter Code:

Sub Construction_Click()

ActiveSheet.Unprotect Password:="OVA"
Range("a12:a1825").AutoFilter Field:=1, Criteria1:="HO"
Range("$e$4").Value = "Filter: Construction"
ActiveSheet.Protect Password:="OVA"
End Sub

Hide Closed Item Filter Code:

Sub HideClosed_Click()
ActiveSheet.Unprotect Password:="OVA"
Range("d13:d1825").AutoFilter Field:=1, Criteria1:=""
Range("$e$4").Value = "ALL (Active Awards Only)"
ActiveSheet.Protect Password:="OVA"
End Sub

 

[Fluff]

You can only have 1 autofilter running at a time so try

Sub Construction_Click()

ActiveSheet.Unprotect Password:="OVA"
Range("a12:d1825").AutoFilter Field:=1, Criteria1:="ba*"
Range("$e$4").Value = "Filter: Construction"
ActiveSheet.Protect Password:="OVA"
End Sub
Sub HideClosed_Click()
ActiveSheet.Unprotect Password:="OVA"
Range("d12:d1825").AutoFilter Field:=4, Criteria1:=""
Range("$e$4").Value = "ALL (Active Awards Only)"
ActiveSheet.Protect Password:="OVA"
End Sub

 

[kathleen0927]

Thanks. This doesn't help because showing the "Closed" rows needs to be an option. Can I add a Yes/ No message box to the discipline macro that if No is selected the rows will be hidden, or is that the same as having 2 filters?

 

[Fluff]

Surely if you want to hide the "closed" rows you simply click the "HideClosed" button, otherwise don't.

 

[kathleen0927]

Thanks. Looks like I can't do what I want which is 2 different (optional) filters. Have a good day!@

 

[Fluff]

Are you saying that you want either col A or col D filtered, but not both?

 

[kathleen0927]

I am saying that the end user may want to filter on col A only, or col D only or filter on both. Right now, all I could do is filter one or the other using separate macros. I was hoping that if they filtered on col d, there would be an option to also filter on col a if desired

 

[Fluff]

In that case how about

Sub Construction_Click()

With ActiveSheet
   .Unprotect Password:="OVA"
   If .AutoFilterMode Then .AutoFilterMode = False
   .Range("a12:d1825").AutoFilter Field:=1, Criteria1:="HO"
   .Range("$e$4").Value = "Filter: Construction"
   If MsgBox("Do you want to filter closed", vbYesNo) = vbYes Then
      .Range("d12:d1825").AutoFilter Field:=4, Criteria1:=""
      .Range("$e$4").Value = "ALL (Active Awards Only)"
   End If
   .Protect Password:="OVA"
End With
   
End Sub

 

[kathleen0927]

That worked perfectly!!! I kept getting errors at Y/N message. Thank you so much for the extra effort to help me. Have a great day!

 

[Fluff]

Glad to help & thanks for the feedback