How can I use VBA to ignore green triangle error in range without looping cell by cell?

Question

I have some large data sets that I am automating and distributing. I want to eliminate the little green triangles that warn the user about numbers stored as text. I have used the following code but it's VERY slow on massive sheets.

     Range(Cells(1, 1), Cells(lastrow, lColumn)).Select
     'kill those dang green cell triagles
     Dim rngCell As Range, bError As Byte
         For Each rngCell In Selection.Cells

             For bError = 3 To 3 Step 1

                 With rngCell
                     If .Errors(bError).Value Then
                         .Errors(bError).Ignore = True
                     End If
                 End With
             Next bError
         Next rngCell

As you can see I already cut it down to 1/7th of the time by not looping through every error just the one I am trying to prevent but it's still VERY slow.

Also I already know about

     Application.ErrorCheckingOptions.NumberAsText = False

But I don't want to use it as I do not want to change users system settings. I want the effect of the loop without looping through all cells. Can I some how tell Excel to stop checking an entire range without looping cell by cell?

Any effective and fast way to do this would be very helpful. Thank you in advance!!

Answer 1

The preferred solution would be to convert the string to a number before you bring it into Excel. For example, when I am working with SQL and I have some numerical values stored as a NVARCHAR in the database I will use a CONVERT(int, colName) in the SQL statement when I am bringing it into Excel. This brings it in as a number and I no longer get that message.

Now, if this sort of option isn't available to you, you can fix that error another way. Simply set the range of values that have the number stored as text error equal to itself.

For example:

Sub Test()

    Sheets("Sheet1").Range("A1:A3").Value = Sheets("Sheet1").Range("A1:A3").Value

End Sub

Where A1:A3 in this example is the range of values you want to no longer store as text.

Since your numbers have leading zeroes, you can change the formatting of these cells to add these zeroes as such:

Sub Test()

    Sheets("Sheet1").Range("A1:A3").Value = Sheets("Sheet1").Range("A1:A3").Value
    'This assumes your numbers are 11 digits long
    'Thus 11132 would display as 00000011132
    Sheets("Sheet1").Range("A1:A3").NumberFormat = "00000000000"

End Sub

This will change the display to show leading zeroes again. If you are exporting this data in any fashion you may have to take steps to ensure that this particular column is exported as text and not number, but I can't help much more without specifics.