Filter code not working

K

KatKitKat

New Member
Joined
Apr 27, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
hi guys,

I am stumped on why my code is not working and could use another set of eyes to look and possibly see where my mistake is. I have a spreadsheet that I want to filter using a command button. I have 2 fields to select the filter. I have drop down lists in cells F3 and G3 that the user will make their selections then hit the button for the spreadsheet to be filtered on both criteria. Below is the start of the code (there can be 24 combinations of selections when complete). When I test this, nothing happens. The sheet does not filter and no error message, which makes me think I have done something stupid and just can't see it. Any input would help. Thank you!

Kat

VBA Code: 
Sub Button37_Click() 'filter Exp Log by Impact Cost and Status
    ActiveSheet.Unprotect Password:="1234"
    Dim list1 As String, list2 As String
   
    list1 = Range("F3")
    list2 = Range("G3")
       
    If list1 = "Open" And list2 = "1 Construction" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="1 Construction"
        End With
        ElseIf list1 = "Open" And list2 = "2 Architectural & Engr" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="2 Architectural & Engr"
        End With
        ElseIf list1 = "Open" And list2 = "3 FF&E" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="3 FF&E"
        End With
        ElseIf list1 = "Open" And list2 = "4 Security & Equipment" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="4 Security & Equipment"
        End With
        ElseIf list1 = "Open" And list2 = "5 One Time Expenses" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="5 One Time Expenses"
        End With
        ElseIf list1 = "Open" And list2 = "7 Program Staffing" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="7 Program Staffing"
        End With
        ElseIf list1 = "Open" And list2 = "8 Program Artwork" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="8 Program Artwork"
        End With
        ElseIf list1 = "Open" And list2 = "P PROGRAM COST" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:="P PROGRAM COST"
        End With
        ElseIf list1 = "Open" And list2 = "ALL" Then
        With Worksheets("ExposureLog").Range("A7:S2508")
        .AutoFilter Field:=13, Criteria1:="Open"
        .AutoFilter Field:=6, Criteria1:=Array("1 Construction", "2 Architectural & Engr", "3 FF&E", "4 Security & Equipment", "5 One Time Expenses", "7 Program Staffing", "8 Program Artwork", "P PROGRAM COST"), _
        Operator:=xlFilterValues
        End With        'End of OPEN
        End If
    ActiveSheet.Protect Password:="1234"
End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
You can slim that code down like
VBA Code: 
Sub Button37_Click() 'filter Exp Log by Impact Cost and Status
 ActiveSheet.Unprotect Password:="1234"
 Dim list1 As String, list2 As String

 list1 = Range("F3")
 list2 = Range("G3")
   
   With Worksheets("ExposureLog").Range("A7:S2508")
      If list1 = "Open" Then
        .AutoFilter Field:=13, Criteria1:="Open"
         If list2 = "ALL" Then
            .AutoFilter 6, Array("1 Construction", "2 Architectural & Engr", "3 FF&E", "4 Security & Equipment", "5 One Time Expenses", "7 Program Staffing", "8 Program Artwork", "P PROGRAM COST"), xlFilterValues
         Else
            .AutoFilter 6, list2
         End If
      End If
   End With

   ActiveSheet.Protect Password:="1234"
End Sub
I can see no obvious reason why it doesn't work, but check that your criteria are exactly as you have in the code as VBA is case sensitive.
 
Upvote 0
Solution
Fluff said:
You can slim that code down like
VBA Code: 
Sub Button37_Click() 'filter Exp Log by Impact Cost and Status
 ActiveSheet.Unprotect Password:="1234"
 Dim list1 As String, list2 As String

 list1 = Range("F3")
 list2 = Range("G3")
  
   With Worksheets("ExposureLog").Range("A7:S2508")
      If list1 = "Open" Then
        .AutoFilter Field:=13, Criteria1:="Open"
         If list2 = "ALL" Then
            .AutoFilter 6, Array("1 Construction", "2 Architectural & Engr", "3 FF&E", "4 Security & Equipment", "5 One Time Expenses", "7 Program Staffing", "8 Program Artwork", "P PROGRAM COST"), xlFilterValues
         Else
            .AutoFilter 6, list2
         End If
      End If
   End With

   ActiveSheet.Protect Password:="1234"
End Sub
I can see no obvious reason why it doesn't work, but check that your criteria are exactly as you have in the code as VBA is case sensitive.
Click to expand...
I will try this. I am working from a spreadsheet someone else started so it is possible that are spaces or something funky happening. I will look again. Thank you.
 
Upvote 0
Hello, I was able to get mine to work, bet then I don't have your workbook. I couldn't help but try to improve the code a little. You should debug the code line by line and see when your code stops working.

VBA Code: 
Sub Button37_Click()
  Dim list1 As String, list2 As String
  Dim Sht As Worksheet
   
  ActiveSheet.Unprotect Password:="1234"
  Set Sht = Sheets("ExposureLog")
  list1 = Range("F3").Value
  list2 = Range("G3").Value
  
  On Error Resume Next
  Sht.ShowAllData
  On Error GoTo 0
  
  If list1 <> "Open" Then Exit Sub
       
  With Sht.Range("A7:S2508")
    If list1 <> "" Then .AutoFilter Field:=13, Criteria1:=list1
    If list2 <> "" And list2 <> "All" Then
      .AutoFilter Field:=6, Criteria1:=list2
    ElseIf list2 = "All" Then
      .AutoFilter Field:=6, Criteria1:=Array("1 Construction", "2 Architectural & Engr", "3 FF&E", "4 Security & Equipment", "5 One Time Expenses", "7 Program Staffing", "8 Program Artwork", "P PROGRAM COST"), Operator:=xlFilterValues
    End If
  End With
    
  ActiveSheet.Protect Password:="1234"
End Sub
 
Upvote 0
Jeffrey Mahoney said:
Hello, I was able to get mine to work, bet then I don't have your workbook. I couldn't help but try to improve the code a little. You should debug the code line by line and see when your code stops working.

VBA Code: 
Sub Button37_Click()
  Dim list1 As String, list2 As String
  Dim Sht As Worksheet
  
  ActiveSheet.Unprotect Password:="1234"
  Set Sht = Sheets("ExposureLog")
  list1 = Range("F3").Value
  list2 = Range("G3").Value
 
  On Error Resume Next
  Sht.ShowAllData
  On Error GoTo 0
 
  If list1 <> "Open" Then Exit Sub
      
  With Sht.Range("A7:S2508")
    If list1 <> "" Then .AutoFilter Field:=13, Criteria1:=list1
    If list2 <> "" And list2 <> "All" Then
      .AutoFilter Field:=6, Criteria1:=list2
    ElseIf list2 = "All" Then
      .AutoFilter Field:=6, Criteria1:=Array("1 Construction", "2 Architectural & Engr", "3 FF&E", "4 Security & Equipment", "5 One Time Expenses", "7 Program Staffing", "8 Program Artwork", "P PROGRAM COST"), Operator:=xlFilterValues
    End If
  End With
   
  ActiveSheet.Protect Password:="1234"
End Sub
Click to expand...
Thanks, I am wondering if there is a data issue somewhere that is causing this. Back to the drawing board. Thank you for your help.
 
Upvote 0
Fluff said:
You can slim that code down like
VBA Code: 
Sub Button37_Click() 'filter Exp Log by Impact Cost and Status
 ActiveSheet.Unprotect Password:="1234"
 Dim list1 As String, list2 As String

 list1 = Range("F3")
 list2 = Range("G3")
  
   With Worksheets("ExposureLog").Range("A7:S2508")
      If list1 = "Open" Then
        .AutoFilter Field:=13, Criteria1:="Open"
         If list2 = "ALL" Then
            .AutoFilter 6, Array("1 Construction", "2 Architectural & Engr", "3 FF&E", "4 Security & Equipment", "5 One Time Expenses", "7 Program Staffing", "8 Program Artwork", "P PROGRAM COST"), xlFilterValues
         Else
            .AutoFilter 6, list2
         End If
      End If
   End With

   ActiveSheet.Protect Password:="1234"
End Sub
I can see no obvious reason why it doesn't work, but check that your criteria are exactly as you have in the code as VBA is case sensitive
Click to expand...
 
Upvote 0
KatKitKat said:
I will try this. I am working from a spreadsheet someone else started so it is possible that are spaces or something funky happening. I will look again. Thank you.
Click to expand...
I used your coding and got an Autofilter method... error message. I had the print range filtered and that prevented the range from doing what I needed. Took that previous filter off and then your code worked! I must have a typo in one of the selections in list 2 that caused the problem. Thank you again! I am learning so much from this site. Have a wonderful day! Kat
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Fluff said:
Glad we could help & thanks for the feedback.
Click to expand...
The "ALL" option isn't filtering so I think that is where there are data mismatches. Is there a way to say if ALL is selected, don't filter on this field? Those are all the options available.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Removing Autofilter Without Resetting All Filters
Replies
3
Views
150
RoryA
RoryA
L
  • Solved
Filtering upon opening document without using VB code
Replies
1
Views
200
Joe4
Joe4
L
  • Solved
VBA code checks a condition, stops macro if FALSE, etc.
Replies
3
Views
126
Peter_SSs
Peter_SSs
V
  • Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
36
VanGirl
V
K
  • Solved
Else If Error
2
Replies
10
Views
643
Fluff
Fluff

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top