Auto filter a table based on the value of a cell

[R3Z]

Hi all I'm using this code that I saw in another thread:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("C2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("D2")) Is Nothing Then
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("B2").Value & "*"
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=2, Criteria1:=Range("C2").Value & "*"
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=3, Criteria1:=Range("D2").Value & "*"
End If
End Sub

This is when I try to add more rows to the table when I try to search the in B2 I don't get the results for the new data.
I don't know if this helps but the cells that are giving me results show up this error: "this number in this cell is formatted as text or preceded by an apostrophe" the new rows don't show up this error and I can't get the results when I search them on B2 (for example).
Please Help me I not experienced person in Excel but I really need a solution.
Thank You.

 

[mohadin]

Range(Range("B5"), Range("B5").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("B2").Value & "*"

    Range(Range("C5"), Range("C5").End(xlDown)).AutoFilter Field:=2, Criteria1:=Range("C2").Value & "*"

    Range(Range("D5"), Range("D5").End(xlDown)).AutoFilter Field:=3, Criteria1:=Range("D2").Value & "*"

End If

 

[R3Z]

Range(Range("B5"), Range("B5").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("B2").Value & "*"

    Range(Range("C5"), Range("C5").End(xlDown)).AutoFilter Field:=2, Criteria1:=Range("C2").Value & "*"

    Range(Range("D5"), Range("D5").End(xlDown)).AutoFilter Field:=3, Criteria1:=Range("D2").Value & "*"

End If

Hi thanks for the reply, that didn't change anything the code works the same and if I add more rows to the table they still doesn't show up when I search the data in B2,C2 or D2.

 

[Alex Blakenburg]

Hi thanks for the reply, that didn't change anything the code works the same and if I add more rows to the table they still doesn't show up when I search the data in B2,C2 or D2.

Can you please clarify what you are trying to achieve.
In the last line of your last post it indicates you are expecting the line to show up if ANY of the 3 values match (OR)
Your current filter will only show rows where ALL 3 values match on the same row.

Also you are using xldown on column D, this means that Column D must have data from the first row through to the last row of the range you want to apply the filter to, is that actually the case. Which column will always have data on all rows ?

 

[R3Z]

Can you please clarify what you are trying to achieve.
In the last line of your last post it indicates you are expecting the line to show up if ANY of the 3 values match (OR)
Your current filter will only show rows where ALL 3 values match on the same row.

Also you are using xldown on column D, this means that Column D must have data from the first row through to the last row of the range you want to apply the filter to, is that actually the case. Which column will always have data on all rows ?

I will try to explain the best i can
So I have this table:

SO	Fab	Ct
Enter Search Value - (B2)	Enter Search Value - (C2)	Enter Search Value - (D2)

24817	SER	ZAL
25414
24818	SER	ZAL

When I enter the search Value under "SO" I want to see all data on the table that equals for example: 24817, and with the code that I have on top this works.
Thing is I add new rows: 25414 to the table and when I search for this number the filter don't work for the new rows.

 

[Alex Blakenburg]

If you only want to filter on B2 or C2 or D2 depending on which one you are changing, then try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range
Dim lrowData As Long
Dim colTarget As Long

If Not Application.Intersect(Target, Range("B2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("C2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("D2")) Is Nothing Then
   
    Application.EnableEvents = False
    If Me.FilterMode Then Me.ShowAllData
    
    lrowData = Columns("B:D").Cells.Find(What:="*" _
            , Lookat:=xlPart _
            , LookIn:=xlFormulas _
            , searchorder:=xlByRows _
            , searchdirection:=xlPrevious).Row

    Set rngData = Range("B5:D" & lrowData)
    
    With rngData
        colTarget = Target.Column - 1
        .AutoFilter Field:=colTarget, Criteria1:=Cells(2, colTarget).Value & "*"
    End With
    
    Application.EnableEvents = True
End If

End Sub

 

[R3Z]

If you only want to filter on B2 or C2 or D2 depending on which one you are changing, then try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range
Dim lrowData As Long
Dim colTarget As Long

If Not Application.Intersect(Target, Range("B2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("C2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("D2")) Is Nothing Then
  
    If Me.FilterMode Then Me.ShowAllData
   
    lrowData = Columns("B:D").Cells.Find(What:="*" _
            , Lookat:=xlPart _
            , LookIn:=xlFormulas _
            , searchorder:=xlByRows _
            , searchdirection:=xlPrevious).Row

    Set rngData = Range("B5:D" & lrowData)
   
    With rngData
        colTarget = Target.Column - 1
        .AutoFilter Field:=colTarget, Criteria1:=Cells(2, colTarget).Value & "*"
    End With
End If
End Sub

Hi, thanks for the reply, I get an error:

Method 'showalldata' of object'_worksheet' failed

 

[Alex Blakenburg]

Are you using and Excel Table ?
If so what is the Table name and the range of the table ?

 

[R3Z]

Are you using and Excel Table ?
If so what is the Table name and the range of the table ?

Yes it's a table.
Name of the table: DataTable
Range: $B$4:$N$177
But I want to be able to add more rows

 

[Alex Blakenburg]

OK try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tblData As ListObject
Dim colFltr As Long

If Not Application.Intersect(Target, Range("B2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("C2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("D2")) Is Nothing Then
   
    Application.EnableEvents = False
    
    Set tblData = Me.ListObjects("DataTable")
    
    With tblData.AutoFilter
        If .FilterMode Then .ShowAllData
    End With
    
    With tblData
        colFltr = Target.Column - tblData.Range.Cells(1).Column + 1
        .Range.AutoFilter Field:=colFltr, Criteria1:=Target.Value & "*"
    End With
    
    Application.EnableEvents = True
End If

End Sub