"<>0" filter error: Not removing "0"/zero values

[ams56]

Hi all,
I have been searching and attempting different approaches but have been unsuccessful in this.
I believe I have attached a simplified example file, and have copied and pasted my code and an image of the incorrect result below as well.
My first line for "123456789" works correctly, but the second is not filtering out "0" values while keeping all other unique values.
Any help or suggestions you can offer would be greatly appreciated.
Thanks in advance.

Sub FilterTab()
'
' FilterTab Macro
'
On Error Resume Next

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:="123456789", Operator:=xlFilterValues
ActiveSheet.Range("$B$1").AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlFilterValues

End Sub

FilterErrorImageOriginal.jpg

[MrShorty]

The first thing I notice is the green triangle on all of the values in the second field, indicating a "number stored as text" error. I suspect that, when you try to set a criteria like "<>0" the filter is looking for the number 0 and not the text string "0". I see no way to get the filter to search for the text "0" If I convert the numbers stored as text to numbers, the filter for <>0 works just fine.

I think a lot is going to depend on why you have stored these ID numbers as text strings rather than numbers.

[TMS]

Very odd. The value in B64 is not being recognised as 0. Try =0=B64. It returns FALSE. However, it returns 48 for CODE(B64) and 1 for LEN(B64). Even =CODE(B62)=48 returns TRUE. And the formatting is consistent, though that shouldn't make any difference. One of Excel's little foibles.

[ams56]

Thanks for calling this out MrShorty.
I added in some additional code that first executes "Texts to Columns" before filtering, which now does what I need it to.
See below for final VBA code.
As far as why the numbers are text strings, we are pulling these reports/excel files from a third party vendor's database and this is how they appear after download. So this is what we have to work with unfortunately. But thanks for again for helping me find a simple workaround so that we don't have to waste time with them to update their database and reports.
Have a great day.

Sub FilterTab()
'
' FilterTab Macro
'
Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

On Error Resume Next

Range("$B$2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("$B$2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:="123456789", Operator:=xlFilterValues
ActiveSheet.Range("$B$1").AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlFilterValues

End Sub

[ams56]

Thanks TMS.
It is strange and as you can see from my above reply quite annoying.
Luckily I at least now have a workaround to deal with this.
Enjoy your day.

[TMS]

You're welcome.