• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VBA to deselect filter criteria

ShawnExcel

Member
I have a 300 question long survey where some questions are dependent on the answer of another question. Essentially, if the person selects "There are no CCTV cameras" then we don't need the following 25 questions about cameras. I have each question categorized by it's heading (CCTV, etc.) so it should be as easy as deselecting a criteria in the filter. I am looking for an automated way to do this without making them open up the filter and select/deselect certain headers.

The problem: When recording a macro, the code to "deselect" one of the criteria shows all the selected ones. This interferes with the ability to answer 1 question as (no CCTV) and then hide those questions, and then another as (no Elevator) and hide those questions as well. What if you answer one and not the other? There's no way for the No Elevator code to know whether or not CCTV should show up or not

Here is the code I'm working with - any ideas?

Code:
Sub Hide_CCTV()
'
' Hide_CCTV Macro
'

'
    Sheets("PHYSICAL Q").Select
    ActiveSheet.ListObjects("MovieTable").Range.AutoFilter Field:=19, Criteria1 _
        :=Array("Elevators", "Intrusion Detection System (IDS)", _
        "Loading Dock / Garbage Area", "Natural Gas", "Parking Areas", _
        "Security Personnel", "Security Station", "UPDATE THE ANSWERS", "UPS System", "=") _
        , Operator:=xlFilterValues
End Sub
 
Back
Top