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 obvious answer (Range(...).Errors(3).Ignore = True) doesn't seem to work when the Range is larger than a single cell.

After a bit of experimentation, I found that you can manually select the range of cells and click the little pop-up menu that appears and tell it to ignore all errors in the range, but this doesn't appear to have a VBA equivalent.

Doing this experiment with the macro recorder on records nothing, which is usually a sign that the programmer at Microsoft who implemented this functionality was incompetent.

Sadly, I think this means that there is no solution other than looping.

Related