Need some help with search function

[smichael]

I'm working with an inventory database and would like a search function to be similar to the "filter" function. I have code (found on another topic) that will do the search, but selects each cell one at a time. I would ideally like the code to filter out all of the cells, and only display the responses to the search. It would be great if somebody could tell me how to do this.

Thanks!

Here is the code:

Public Sub FindText1()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range
Dim rngNm As String, myText As String, FirstAddress As String
Dim AddressStr As String, thisLoc As String
Dim foundNum As Long
Dim myF As Variant, myRD As Variant
myAgain:
myText = ""
FirstAddress = ""
foundNum = 0
rngNm = ""
AddressStr = ""
thisLoc = ""
myF = ""
myRD = ""
myText = InputBox("Enter text to find")
If myText = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(What:=myText, LookIn:=xlValues, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address
Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select
myF = MsgBox("Found one """ & myText & """ here!" & vbLf & vbLf & _
thisLoc, vbInformation + vbOKCancel, "Found!")
If myF = 2 Then GoTo myQuit
Set Found = .UsedRange.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
Next ws
If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbLf & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
myEnd:
myRD = MsgBox("Search Again?", vbInformation + vbOKCancel, "Re-Run?")
If myRD = 1 Then GoTo myAgain
myQuit:
End Sub

 

[smichael]

I converted the last column to a range and it works perfectly now. Thanks for everything!