PDA

View Full Version : Filter cells that contain at least a certain string



circaa
10-28-2006, 02:43 PM
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
10-28-2006, 03:04 PM
Just do a custom flter selection using the contains operation.

lucas
10-28-2006, 06:14 PM
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
10-28-2006, 07:20 PM
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
10-28-2006, 07:27 PM
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
10-28-2006, 07:35 PM
Also do you just want one date or a range between two dates:
this works for a range between 2 dates:

Option Explicit
Sub ApplyFilter()
Range("A4:D58").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("G1:H2"), Unique:=False
End Sub
Sub RemoveFilter()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub

Bob Phillips
10-29-2006, 02:47 AM
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



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