How can I automatically update one filter without updating other filters?-CodePudding

I have an Excel sheet with filters in columns A and B. I am looking to develop a macro that will automatically update the filter in column B whenever the data in this worksheet are updated, but will not automatically update the filter in column A. Here is the code I currently have:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Range("$A:$B").AutoFilter Field:=2
    ActiveSheet.Range("$A:$B").AutoFilter Field:=2, Criteria1:="<>x", _
        Operator:=xlAnd
End Sub

As intended, this macro updates the filter in column B whenever the data are updated, such that any cell containing "x" is automatically filtered out. However, this macro also automatically updates the filter in column A. This is problematic because if I have already applied a filter in column A, and if I then insert a new row, the macro automatically hides the new row because the blank values in the new row do not meet the filtering criteria for column A. I would like the macro to automatically update the filter in column B without also updating the filter in column A, so that inserting a new row will not trigger the macro to immediately hide this new row.

Thanks very much for any input!

CodePudding user response：

I have now found one workaround in which I clear the filter in column A and then add formulas in column C, such that these formulas output TRUE if the value in column A meets the desired criterion or is blank, and outputs FALSE otherwise. Then I modified the macro as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ActiveSheet.Range("$A:$C").AutoFilter Field:=3
    ActiveSheet.Range("$A:$C").AutoFilter Field:=3, Criteria1:="TRUE", _
        Operator:=xlOr, Criteria2:=""
        
    ActiveSheet.Range("$A:$C").AutoFilter Field:=2
    ActiveSheet.Range("$A:$C").AutoFilter Field:=2, Criteria1:="<>x", _
        Operator:=xlAnd
        
End Sub

This macro now filters out rows that display "x" in column B, but also allows new rows to be inserted without immediately filtering out these rows. There may be an easier solution than this, but this seems at least adequate for my purposes.