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

 

[MARK858]

Try the below if your data is in column A, test on a copy of your data as you are deleting.
Btw, you are better off starting a new thread rather than hijacking an old thread as your question won't appear in the zero reply posts (link to the old thread if you think it is relevant).

Sub RemoveText()
    With Columns("A:A")
        .SpecialCells(xlCellTypeConstants, 2).Delete Shift:=xlUp
        .RemoveDuplicates Columns:=1, Header:=xlNo
    End With
End Sub

or

Sub RemoveText2()
    With Columns("A:A")
        .SpecialCells(xlCellTypeConstants, 2).Delete Shift:=xlUp
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    End With
End Sub

 

[xargon05]

Thanks Mark858 for the tip about linking an old thread in a new post. I'll be sure to do that in future.

I should've clarified, I want to filter to hide the text so I can copy to a new sheet and then remove the filter. Something similar to firefly's method in this thread, but I want to keep the numbers instead of hiding them.

 

[MARK858]

I don't think you can do that with filter, I think you will have to hide the rows.

Sub RemoveText()
    With Columns("A:A")
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        .SpecialCells(xlCellTypeConstants, 2).EntireRow.Hidden = True
    End With
End Sub

 

[Fluff]

You could try "<>*"