AutoFilter Yes / No based on numerical value

[nicespreadsheetmate]

Hi! Some fantastic info on these forums, thanks all. Unfortunately I wasn't able to find a response to this specific issue in my macro.

I'm creating a spreadsheet for work that is populated with all of our projects and their embodied carbon rankings. The user will be able to search and filter the data based on drop down strings. This is all fine. Unfortunately, one of filters will be - if the user selects Basement = yes, filter all the data where the data in the "Below Ground" column > 0. Basically it works on the assumption that if the below ground value is greater than 0 then the building has a basement.

sht.Range("A2:T2").AutoFilter field:=14, Criteria1:="=*" & sht2.Range("Dashboard!$C2").Value & "*"
sht.Range("A2:T2").AutoFilter field:=13, Criteria1:="=*" & sht2.Range("Dashboard!$C3").Value & "*"
sht.Range("A2:T2").AutoFilter field:=20, Criteria1:="=*" & sht2.Range("Dashboard!$C4").Value & "*"

C4 in the above is the Yes or No drop down option, Field 20 is a numeric value either 0 or greater than 0.

Thanks kindly,

Rob

 

[Alex Blakenburg]

See if this does what you need:-

    sht.Range("A2:T2").AutoFilter field:=14, Criteria1:="=*" & sht2.Range("Dashboard!$C2").Value & "*"
    sht.Range("A2:T2").AutoFilter field:=13, Criteria1:="=*" & sht2.Range("Dashboard!$C3").Value & "*"
    
    ' Has Basement Y/N
    Select Case UCase(sht2.Range("Dashboard!$C4").Value)
        Case "YES"
            sht.Range("A2:T2").AutoFilter field:=20, Criteria1:=">0"
        Case "NO"
            sht.Range("A2:T2").AutoFilter field:=20, Criteria1:="=0"
        Case Else
            sht.Range("A2:T2").AutoFilter field:=20
    End Select

 

[nicespreadsheetmate]

See if this does what you need:-

    sht.Range("A2:T2").AutoFilter field:=14, Criteria1:="=*" & sht2.Range("Dashboard!$C2").Value & "*"
    sht.Range("A2:T2").AutoFilter field:=13, Criteria1:="=*" & sht2.Range("Dashboard!$C3").Value & "*"
   
    ' Has Basement Y/N
    Select Case UCase(sht2.Range("Dashboard!$C4").Value)
        Case "YES"
            sht.Range("A2:T2").AutoFilter field:=20, Criteria1:=">0"
        Case "NO"
            sht.Range("A2:T2").AutoFilter field:=20, Criteria1:="=0"
        Case Else
            sht.Range("A2:T2").AutoFilter field:=20
    End Select

Works a treat, thank you for taking the time.

Best,
Rob

 

[Alex Blakenburg]

Glad I could help & thanks for the feedback.