Filtering ranges of cells in Excel,

[Nicki]

Please help with the vba code on command button. Many Thanks

 

[Chihiro]

You need to give bit more detail than that. Which column is to be filtered and with what condition? Better yet, upload sample workbook that represents your data set.

 

[Nicki]

You need to give bit more detail than that. Which column is to be filtered and with what condition? Better yet, upload sample workbook that represents your data set.

Hi Chihiro, many thanks for looking at my question. I'd like to type any word in search box from table called PA as it shown in picture then to be able to see the whole row of information in the result box. for example. if i type Diane Which is in G28 in PA table id like the whole row to be shown in result table. Thanks


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Nicki]

Could you please help me with this. I d like to be able to filter from different Filed not just Field 1. i.e to filter from range C6:g19 rather than just Field 1. see below in red

Private Sub TextBox1_Change()
ActiveSheet.ListObjects("ContactList").Range.AutoFilter Field:=1, _
Criteria1:="*" & [e3] & "*", Operator:=xlFilterValues
Please see the excel sheet attached. Many Thanks

 

[Chihiro]

Haven't fully tested it, but try...

Private Sub TextBox1_Change()
Dim c, col As Long
With ActiveSheet.ListObjects("ContactList").DataBodyRange
    Set c = .Find([E3].Value, .Cells(1), xlValues, xlPart, xlByRows)
    If Not c Is Nothing Then
        col = c.Column - 2
    ElseIf Len([E3].Value) = 0 Then
        Call clearFilter
        Exit Sub
    Else
        Exit Sub
    End If
End With
ActiveSheet.ListObjects("ContactList").Range.AutoFilter Field:=col, _
Criteria1:="*" & [E3] & "*", Operator:=xlFilterValues
 
End Sub

Sub clearFilter()
    [E3] = ""
    ActiveSheet.ListObjects("ContactList").AutoFilter.ShowAllData
End Sub

FYI - I don't usually check the forum over the weekend.

 

[Nicki]

Hi Chihiro. Many Thanks for helping me. it is just this section

End with
ActiveSheet.ListObjects("ContactList").Range.AutoFilter Field:=col, _
Criteria1:="*" & [E3] & "*", Operator:=xlFilterValues

gives the runtime error 1004 application-defined or object defined error

 

[Chihiro]

Hmm, working fine on my end. See attached, built using your sample.

Though I noticed that I should have added line to reset filter at start (otherwise, once partial match is found on 1st column, it won't be able to find match in 2nd column if you type more).

Code amended to below.

Private Sub TextBox1_Change()
Dim c, col As Long
With ActiveSheet.ListObjects("ContactList")
    .AutoFilter.ShowAllData
    With ActiveSheet.ListObjects("ContactList").DataBodyRange
        Set c = .Find([E3].Value, .Cells(1), xlValues, xlPart, xlByColumns)
        If Not c Is Nothing Then
            col = c.Column - 2
        ElseIf Len([E3].Value) = 0 Then
            Call clearFilter
            Exit Sub
        Else
            Exit Sub
        End If
    End With
    .Range.AutoFilter Field:=col, Criteria1:="*" & [E3] & "*", Operator:=xlFilterValues
End With
End Sub

Sub clearFilter()
    [E3] = ""
    ActiveSheet.ListObjects("ContactList").AutoFilter.ShowAllData
End Sub

 

[Nicki]

Many Thanks Chihiro for your time and help.