Autofilter for a specific number

[MCC123]

Hi all, I created a repair sheet that involves 47 cars each assigned a number. I have a command button that will add a new line to a table each time a car(s) need a repair. I would like to be able to auto filter the sheet based on the car number via a command button or message box to where I enter the number and it automatically will bring up each row with that specific number (which is located in the 3rd field or column) in it and hide the rest. Than once I complete my report I will need to undo the filter. Any help would be greatly appreciated!!

 

[My Aswer Is This]

Assuming you have a Table named "Cars" on the active sheet.
And you want to filter on the third column of the Table
This script will ask for a Car number to look for in Column 3 of the table and do the filter.
When your done and want to unfilter the table just run the same script again and the filter will be removed.

Modify table name if needed.

Sub Filter_Me()
'Modified  11/19/2019  6:56:28 AM  EST
Dim ans As String

If ActiveSheet.FilterMode Then
    ActiveSheet.ListObjects("Cars").Range.Cells(1, 3).AutoFilter

Else
    ans = InputBox("Enter car number")
    ActiveSheet.ListObjects("Cars").Range.AutoFilter Field:=3, Criteria1:=ans
End If
End Sub

 

[MCC123]

?Works GREAT!! Thanks My Aswer Is This

 

[My Aswer Is This]

?Works GREAT!! Thanks My Aswer Is This

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[MCC123]

I will, and I'm back with one more..........with the original post same scenario instead of one car is there any way to filter between a range of cars. in my case in the message box It would ask me enter car numbers instead of number? Thanks again for your knowledge!!! look forward to your answer, My Aswer Is This!

 

[My Aswer Is This]

I will get back with you

 

[My Aswer Is This]

So assuming you only want to enter a few names lets try this:
When your Inputbox pops up enter the car names like this:
Ford,Chevy,BMW,Aldi

See you must place a comma after each name.

Now if you plan to need more then about 5 we should get a better solution.

Sub Filter_Me()
On Error GoTo M
'Modified  11/21/2019  4:50:22 AM  EST
Dim myRange As Range
On Error Resume Next
Dim ans As String
Dim Del As Variant
Dim LString As String
Dim LArray() As String

    If ActiveSheet.FilterMode Then
        ActiveSheet.ListObjects("Cars").Range.Cells(1, 3).AutoFilter
    Else
    MsgBox "When entering values in Inputbox" & vbNewLine & "Place comma after each name" & vbNewLine & "Like this" & vbNewLine & "Ford,Chevy,BMW"
    
        ans = InputBox("Enter Name", "Like This:  BMW, Ford,  Be sure and place , after each name")
        LString = ans
        LArray = Split(LString, ",")
        Del = LArray
        ActiveSheet.ListObjects("Cars").Range.AutoFilter Field:=3, Criteria1:=Del, Operator:=xlFilterValues

        Set myRange = ActiveSheet.ListObjects("Cars").DataBodyRange.SpecialCells(xlVisible)
            If myRange Is Nothing Then
                ActiveSheet.ListObjects("Cars").Range.Cells(1, 3).AutoFilter
                MsgBox "No names found"
            End If
    
    End If
    Exit Sub
M:
    MsgBox "We had a problem.  You may have not entered any names"
    
End Sub