VBA Code Filter

[KatKitKat]

Hi:

I have a spreadsheet where I added a command button to filter the list by criteria and that is working perfectly. I need to add another criteria but can't figure out the logic to do it. Here is the situation I currently have and the code I have which is working.

The spreadsheet tracks records by vendors. I have a row with the vendor name at the beginning of a section with 100 rows, followed by a row where I have subtotals. There are 30 of these sections. In row E, I have an Aging column that is tracking how long the item has been open. I have a drop-down field (cell E4) with the criteria to filter by Aging (<=1 week, >=2 Weeks, >=3 weeks and greater than 1 month. The command button filters the spreadsheet based on what the user selects in this cell, as long as the item status (column H) is equal to "Open". This works.

Now, I need to add code so when one of these filters is selected, I also get the vendor name header row and the subtotal row. These are not fixed positions. I tried adding a "x" in column E or H for these rows and adding them to the code, but it doesn't work. I can add this "x" to any column it that is the best method to filter by, but I can't seem to make it work. Below is my code that works for filtering just by the Aging number and status = Open. Any suggestions for how to get this additional piece of information in would be greatly appreciated.

Thanks Kat

Sub Button28_Click()
ActiveSheet.Unprotect Password:="1234"
Sheets("CMLog").AutoFilterMode = False
Dim list1 As String

list1 = Range("E4")

With Worksheets("CMLog").Range("A9:AC10000")
If list1 = "< = 1 week" Then
.AutoFilter Field:=8, Criteria1:="Open"
.AutoFilter Field:=5, Criteria1:="<1", Criteria2:="1", Operator:=xlOr
End If
If List1 = "> 2 Weeks" Then
.AutoFilter Field:=8, Criteria1:="Open"
.AutoFilter Field:=5, Criteria1:=">2", Criteria2:="2", Operator:=xlOr
End If
If List1 = "> 3 Weeks" Then
.AutoFilter Field:=8, Criteria1:="Open"
.AutoFilter Field:=5, Criteria1:=">3", Criteria2:="3", Operator:=xlOr
End If
If List1 = "> 1 Month" Then
.AutoFilter Field:=8, Criteria1:="Open"
.AutoFilter Field:=5, Criteria1:=">4", Criteria2:="4", Operator:=xlOr
End If
If List1 = "ALL" Then
Range("A9:AC10000").AutoFilter
End If

End With
ActiveSheet.Protect Password:="1234", AllowFiltering:=True

End Sub

 

[DanteAmor]

You could share a file, in sheet1 put all your unfiltered data, in sheet2 put the filtered data, to see how you want the result.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.