Help! How to enable and disable specific column filter options

[jmr9642]

Hello,

I have a set of data that changes every time with column filters at the top of the data. With VBA code, I am looking for a way to make every filter option visible in one column EXCEPT the #N/A, and in the 2nd column, have the only thing visible be the #N/A. So if you look in the attached image, the prod section would have all filters enabled EXCEPT #N/A and the dev section would have all filters disabled EXCEPT the #N/A.

The tricky part is that this data can change from time to time, so hard coding the filter names needs to be avoided. Is there a select all and/or deselect all function in VBA that could handle this?

NOTE: The columns are populated via VLOOKUPs.

I am basically looking for a way to handle the column filters dynamically to enable all except #N/A for one column, and for the other, disable all except the #N/A.

Please let me know what approach should be taken here! Thank you for your time in advance.

 

[MARK858]

You haven't stated what your ranges are or showed them in your image (you would be better off showing your ranges using the boards XL2BB addin, which gives us all the data needed and allows us to copy your data into Excel, which an image doesn't).

Any way you could have done this using the macro recorder to get the code (unless your data is in a defined table which you haven't said it is but the quote " so hard coding the filter names needs to be avoided" makes me suspicious that it is ).

A tidied up version of what the recorder produces is below (change the range to suit).

Sub Macro1()
    With Range("A1:B14")
        .AutoFilter 1, "<>#N/A"
        .AutoFilter 2, "=#N/A"
    End With
End Sub