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!!
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.