Show "Number stored as Text" error

Question

I'm using an Advanced Filter, among others, with 0 and 1's. The filter works correctly only if in the Data sheet the cells with 1 and 0 have the error message "Number stored as text" displayed. For that I have to manually open the cell with double click and press Enter. Then the error message appears and the filter works. If I don't do that, the filter doesn't work.

Alternatively I can click on the cell with 1 or 0 and press F2 to display the error message.

Is there a way with VBA that I can automatically do that?

Many thanks for your help!

Answer 1

You'll find that buried deep in the Range properties for the cells, specifically in the Errors collection. Just find the cells where that error is present, then set the Ignore property to True:

Public Sub IgnoreNumsAsText()
    Dim current As Range
    For Each current In ActiveSheet.UsedRange.Cells
        With current
            If .Errors.Item(xlNumberAsText).Value = True Then
                .Errors.Item(xlNumberAsText).Ignore = True
            End If
        End With
    Next current
End Sub