Automatically run Macro after changing the criteria cell RRS feed

  • Question

  • Hello,

    I have the following problem:

    I made the code (below) and it works!
    The criterial is the cell (B1) which is in the same Excel sheet.
    But if I change the content of this cell B1, the Macro doesn't work automatically.
    I have to do it manually.

    I searched, found and tried many private subs but without any success.
    I actually have no idea what sub or code to use and where to put this private sub.

    I really hope someone can help me!

    Kind regards,

    Sub filter ()
    Range("A2").AutoFilter field:=2, Criteria1:=Range("B1"), VisibleDropDown:=True

    With Range("A3:AC3")
    .AutoFilter field:=1, VisibleDropDown:=False
    .AutoFilter field:=3, VisibleDropDown:=False
    .AutoFilter field:=4, VisibleDropDown:=False
    .AutoFilter field:=5, VisibleDropDown:=False
    .AutoFilter field:=6, VisibleDropDown:=False
    .AutoFilter field:=7, VisibleDropDown:=False
    .AutoFilter field:=8, VisibleDropDown:=False
    .AutoFilter field:=9, VisibleDropDown:=False
    .AutoFilter field:=10, VisibleDropDown:=False
    .AutoFilter field:=11, VisibleDropDown:=False
    .AutoFilter field:=12, VisibleDropDown:=False
    .AutoFilter field:=13, VisibleDropDown:=False
    .AutoFilter field:=14, VisibleDropDown:=False
    .AutoFilter field:=15, VisibleDropDown:=False
    .AutoFilter field:=16, VisibleDropDown:=False
    .AutoFilter field:=17, VisibleDropDown:=False
    .AutoFilter field:=18, VisibleDropDown:=False
    .AutoFilter field:=19, VisibleDropDown:=False
    .AutoFilter field:=20, VisibleDropDown:=False
    .AutoFilter field:=21, VisibleDropDown:=False
    .AutoFilter field:=22, VisibleDropDown:=False
    .AutoFilter field:=23, VisibleDropDown:=False
    .AutoFilter field:=24, VisibleDropDown:=False
    .AutoFilter field:=25, VisibleDropDown:=False
    .AutoFilter field:=26, VisibleDropDown:=False
    .AutoFilter field:=27, VisibleDropDown:=False
    .AutoFilter field:=28, VisibleDropDown:=False
    .AutoFilter field:=29, VisibleDropDown:=False
    End With
    End Sub
    Tuesday, December 12, 2017 1:20 PM

All replies

  • Right-click the sheet tab.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("B1"), Target) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Call Filter
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub

    Regards, Hans Vogelaar (

    Tuesday, December 12, 2017 1:56 PM
  • I made the code (below) and it works!

    Question: Why so much effort if only one Autofilter in column B is needed?


    Sub filter()
      'Autofilter off
      ActiveSheet.AutoFilterMode = False
      'Create a new one in colum B only
      Range("B2", Range("B" & Rows.Count).End(xlUp)).AutoFilter _
        Field:=1, Criteria1:=Range("B1")
    End Sub

    Tuesday, December 12, 2017 3:18 PM