filters rows cantaining a certain word

[XiniX]

I got a list of proverbs and i want to see only those proverbs containing a certain word. How can i make a filter which will do that?

Thank you...

 

[royUK]

Sub Autofilter()
'
' Macro1 Macro
' Macro recorded 18/10/2002 by Roy Cox
'

'
Selection.Autofilter
ActiveWindow.ScrollColumn = 1
Selection.Autofilter Field:=1, Criteria1:="Your Word"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3

End Sub

This might work.

 

[attc]

for a non-macro based approach, you can also try putting this formula in the column next to your column of proverbs:

=1-ISERROR(FIND("slings and arrows",A1))

Then you can filter on that column.

 

[XiniX]

Thank you, so fast an answer..

I want "Your word" put dynamically in the macro. So that the macro askes for the word to filter...

Is that possible?

 

[rikrak]

On 2002-10-27 09:00, XiniX wrote:
Thank you, so fast an answer..

I want "Your word" put dynamically in the macro. So that the macro askes for the word to filter...

Is that possible?

Hi,

Maybe you can use this macro.
It will show only the rows that contain a specified word.
<pre>
Sub Filter()
Dim searchString As String
Dim found As Range
Dim unionRng As Range

'get string to search
searchString = InputBox("Filter what?", "Filter", "")
If searchString = "" Then Exit Sub

' remove any filters
ActiveSheet.AutoFilterMode = False
' unhide all
saveCellPos = ActiveCell.Address
Cells.Select
Selection.EntireRow.Hidden = False

Set found = Cells.Find(searchString)
If Not found Is Nothing Then
FirstAddress = found.Address
Set unionRng = found
Do
Set found = Cells.FindNext(After:=found)
If found.Address = FirstAddress Then Exit Do
Set unionRng = union(found, unionRng)
Loop

'show only 'rows' containing searchstring
Cells.Select
Selection.EntireRow.Hidden = True
unionRng.EntireRow.Hidden = False
Else
MsgBox searchString & " not found!"
End If
Range(saveCellPos).Select
Set unionRng = Nothing
Set found = Nothing
End Sub
</pre>

 

[XiniX]

Thank you this does the trick indeed...