VBA Autofilter for non-numerical data

[Dobster25]

Good Afternoon peeps,

I have been trying to find a solution to my little problem. I have an autofilter however I can't get it to filter the data which is basically text only.

The numerical data in the selected column is 5 digits long (#####). I want the autofilter to select and show everything else that is not 5 digits long. Hope that makes some sense.

This is what i have so far with regards to my code:

With wBook1.Sheets("T900 Sorted")
        .AutoFilterMode = False
    End With
 
    With wBook1.Sheets("T900 Sorted").Range("A1:R1")
        .AutoFilter
        .AutoFilter field:=2, Criteria1:="<>*#####*"
    End With
 
    wBook1.Sheets("T900 Sorted").AutoFilter.Range.Copy wBook1.Sheets("T900 Unknown").Range("A1")
 
    wBook1.Sheets("T900 Sorted").AutoFilterMode = False

Hopefully this is just a quick and simple fix.

Thanks for taking your time to help me.

Kind Regards

 

[Firefly2012]

Can you please clarify if you actually have numeric data in this column?

 

[Domski]

I think you will need to use a helper column if you want to use autofilter. Either that or loop through the column checking the length of cell values and hide the rows.

Dom

 

[Dobster25]

Firefly,

Yes i have numerical data in the column.

Domski,

With regards to a helper column, is there a way to say, loop through the data and place the number '2' in the helper column? Do you know any code from the top of your head for this? Im thinking somethine like:

Lastrow = .usedrange.rows.count
 
for i = 2 to Lastrow
with range("B" & i)
       if .value = [COLOR=red](not sure how to declare numerical length)[/COLOR] then range("S" & i).value = 2
end with
next i

DO you think you can help me with the criteria?

 

[Firefly2012]

Try it with this criteria:

.AutoFilter field:=2, Criteria1:="=*", Operator:=xlOr, Criteria2:="="

 

[Dobster25]

Firefly,

That works a treat!! thank you.

Could you please explain what each of those criterion represent please so that I and other who view this thread can understand how it works.

Thank you again to you both for your contribution.

10/10 as always.

 

[Firefly2012]

Sure:

Criteria1 ("=*") is a text filter for "one or more of any characters" - so it filters non-zero length text entries. Numeric entries are excluded.

Criteria2 ("=") is a blanks filter so it returns zero-length entries.

So numbers are excluded (error values would be too, if you have any), but everything else returned.

 

[Dobster25]

Wow,

I have to be honest that has to be one of the most powerful filtering tools I have experienced using VBA.

Thank you again for your help!

 

[Morrighanna]

Thank you so very much! Been looking for this answer for about an hour now!!!

 

[xargon05]

Hi folks! way after the fact here, but how might one do the opposite? Remove all Text from a column with some cells as numbers and some with text? I need to get a list of all unique numbers (not text).

Sure:

Criteria1 ("=*") is a text filter for "one or more of any characters" - so it filters non-zero length text entries. Numeric entries are excluded.

Criteria2 ("=") is a blanks filter so it returns zero-length entries.

So numbers are excluded (error values would be too, if you have any), but everything else returned.