Excel AutoFilter macro help

[Lemming Train]

Hi I'm making an auto filter on my spreadsheet and have successfully managed to filter field 1 with the following :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim filterInput As Range
Dim filterRange As Range

Set filterInput = Range("A2")
Set filterRange = Range("A4:A16")

If Not Intersect(Target, filterInput) Is Nothing Then

    filterRange.AutoFilter Field:=1, Criteria1:="*" & filterInput & "*", _
    VisibleDropDown:=False

End If

End Sub

What I can't figure out is how to filter field 2 with a separate filter box as well. I have copied the code and pasted it underneath, but when I change the input and filter ranges to suit, and change AutoFilter to 2, I get a "Complie error : ambiguous name detected : Worksheet_Change"

Any ideas please?

[apparently you can't paste 2 lots of code tags into 1 post]

Edited by Lemming Train on Friday 6th December 20:35

[Lemming Train]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim filterInput As Range
Dim filterRange As Range

Set filterInput = Range("A2")
Set filterRange = Range("A4:A16")

If Not Intersect(Target, filterInput) Is Nothing Then

    filterRange.AutoFilter Field:=1, Criteria1:="*" & filterInput & "*", _
    VisibleDropDown:=False

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim filterInput As Range
Dim filterRange As Range

Set filterInput = Range("B2")
Set filterRange = Range("B4:B16")

If Not Intersect(Target, filterInput) Is Nothing Then

    filterRange.AutoFilter Field:=2, Criteria1:="*" & filterInput & "*", _
    VisibleDropDown:=False

End If

End Sub

[n3il123]

You've got two subs named the same change the worksheet_change to worksheet_change_1 and_2. Or add them together as one sub.

[Lemming Train]

n3il123 said:

You've got two subs named the same change the worksheet_change to worksheet_change_1 and_2. Or add them together as one sub.

Thanks, unfortunately if I change the first sub to anything other than worksheet_change it stops working. Is there a way to combine both search requirements within the same sub and if so how please?

[Anubis]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngFilter_Cell_1 As Range   ' This is our first value to filter by (column A filter 1)
Dim rngFilter_Cell_2 As Range   ' This is our second value to filter by (column B filter 2)
Dim rngFilter_Data As Range     ' This is all of our data (including header row)

    On Error GoTo err

    ' Apply filter if one of these cells change
    Set rngFilter_Cell_1 = Range("A2")
    Set rngFilter_Cell_2 = Range("B2")

    ' This is our range of data including the header
    Set filterRange = Range("A4:B16")

    ' Only alter the filter if we changed either filter value cells
    If Target.Address = rngFilter_Cell_1.Address Or _
        Target.Address = rngFilter_Cell_2.Address Then

        ' Make sure we have a filter if one does not exist
        If Not (ActiveSheet.AutoFilterMode) Then
            filterRange.AutoFilter
        End If

        ' Apply the filter for the relevant column \ field filter
        With filterRange
            .AutoFilter _
            Field:=Target.Column, _
            Criteria1:="*" & Target.Value2 & "*", _
            VisibleDropDown:=False
        End With

    End If

    Exit Sub
err:
    MsgBox err.Number & ": " & err.Description, vbExclamation, "Error: Worksheet_Change"

End Sub

[Lemming Train]

Anubis - thanks a lot for this! Only just spotted the reply in my feed.