Filtering

[alarants]

Hi,
Could anybody say why this works
ActiveSheet.Range("$A$1:$F$64562").AutoFilter Field:=1, Criteria1:="=3100"
but this does not
ActiveSheet.Range("$A$1:$F$64562").AutoFilter Field:=1, Criteria1:="=31*"
I actually need the last one. How to solve the problem?

 

[Kiwiman]

Howzit

It has probably got something to do with the "=31*" where excel would expect a LIKE 31. I don't know the syntax, but you can easily record a macro to find the correct syntax by filtering on Begin With 31 based on your requirement.

 

[Brianwarnock]

What are you testing for
31
312
3123456789
etc

Or

Between 3100 and 3199

Brian

 

[alarants]

I need to filter all numbers that begin with 3

 

[Kiwiman]

Howzit

Ignore my last post it is rubbish. I have done some googling on this and it looks like the Begins with syntax - that you have does not work on numeric fields. The suggestion on most sites was to have a new column, turning the value into a text string using something like =Text(yourcell,"#"), then use the begins with filter on this new column.

 

[peregrin]

^^ sounds right. It must be a text field and you are looking for a greater than criterion. For numbers, 32 is greater than 31, but 9 is not. For text, 32 is greater than 32, and so is 9. Might work to record a macro and see how the macro recorder does it - I don't think you use wildcards in Excel filters but I don't really know.