problem with VBA Filter

[Mcstefan]

Hi,

T

he VBA line below does Filter / Number Filters / Custom Filter / Begins with 1​

ActiveSheet.Range(Selection, Selection.End(xlUp)).AutoFilter Field:=1, Criteria1:="1*"
and the result is nul, which is incorrect, because I was expecting to see a lot of lines that met this criteria.


However, if I manually do in Excel
click on Filter / and in the Search box I type 1*, then I get as a result a lot of lines (which is exactly what I want and what I expected to see).


Can anybody help me to correct the code or tell me why is not working

Thanks.
Cristian

 

[MARK858]

Ugly workaround...
Select your column, Click Data, Text to Columns, click Next twice, check the Text checkbox, Click Finish.
Your macro should work now but this option depends on what you are doing with the data afterwards.

 

[Michael M]

you could also put this formula in an adjacent column and filter by True/false
Change the reference in red to suit the column you are working with

=IF(LEFT([color=red]B1[/color],1)*1=1,"True","False")

 

[Mcstefan]

Hi Mark, is still not working, the result of the filter is still nul. After applying what you suggested, the code did Filter / Text Filters / Begins with / begins with 1 instead of previously

Filter / Number Filters / Custom Filter / Begins with 1​

​

<strike></strike>

 

[MARK858]

It produces the result expected for me (see workbook in link below).

Filter / Text Filters / Begins with / begins with 1

Of course it does because it is a number stored as text now. what did you expect?

https://app.box.com/s/nuebvuddc09gcs3rozajc6kthnoaaw5c

 

[Mcstefan]

thanks guys, both options worked nice for me.