Filter cells that contain at least a certain string

[circaa]

Hi ,

easy one for you guys, I know it's a one line problem but I didn't it ....anyway here it is :

I want to autofilter a column and keep the rows that contain at least my string.

Exemple : criteria1:="Potatoes"

And I want the cells that contain "Small Potatoes" to be kept too.

thanks

Joey

[Bob Phillips]

Just do a custom flter selection using the contains operation.

[lucas]

I'm not sure you can do an xlpart with autofilter or sort(someone correct me if I'm wrong) but with find you can copy the rows to sheet 2. If that might help see the attachment.

[circaa]

Thanks for your answers,

How would you use the contains operation ? as I see it must be applied to a string....how can I use it in my criteria ?

I may have gone the wrong way...basically what i'm trying to do is filter a column of dates and keep the ones that contain the month and year number i'm lookin' for...thanks

Joey

[lucas]

Where would it find the date....hard coded in the macro, in a cell, inputbox, userform....?
why do you ask about contain if its a formatted date column?
If you can let us know what your trying to do it would help us a lot.

[lucas]

Also do you just want one date or a range between two dates:
this works for a range between 2 dates:
[VBA]
Option Explicit
Sub ApplyFilter()
Range("A458").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("G1:H2"), Unique:=False
End Sub
Sub RemoveFilter()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub
[/VBA]

[Bob Phillips]

Filtering dates is very tricky, because the criteria tends to be string type a,nd dates are dates. I find it best to pick up the format like soi

[vba]

Sub AutofilterDates()
Dim sValue1 As String
Dim sValue2 As String
Dim sFormat As String
sFormat = Selection.Cells(2, 1).NumberFormat
sValue1 = "01-Jan-2006"
sValue2 = "01-Feb-2006"
Selection.AutoFilter Field:=1, _
Criteria1:=">=" & Format(sValue1, sFormat), _
Operator:=xlAnd, _
Criteria2:="<" & Format(sValue2, sFormat)
End Sub
[/vba]