Hide "inconsistent formula" error in a selection in Excel

This may seem easy to more advanced users, but I can't seem to find this out there.

I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet.

Unfortunately, these cells trigger the Inconsistent Error Warning (green triangle), and over the few thousand cells, this makes for quite an ugly sheet.

I am aware you can disable this globally, and it is possible to individually disable the checking... but is it possible to hide a range? EG: I4:JQ151. As you can imagine; individually hiding every cell is too much, and global disabling is a minefield for other parts of the sheet that DO need validation.

Cheers in advance, and sorry if this is a nooby question.

3 Answers

You can do this for a range by:

1. selecting the range.
2. Click the arrow next to the button that appears.
3. Select ignore error

Opton 2 via vba: select your cells and run the following macro

Sub Example() Dim rngCell As Range, bError As Byte For Each rngCell In Selection.Cells For bError = 1 To 7 Step 1 With rngCell If .Errors(bError).Value Then .Errors(bError).Ignore = True End If End With Next bError Next rngCell
End Sub

2

Sorry this is old news. Be sure to select a range where the top left cell contains an error. Then you get the yellow pop up needed to "Ignore Error"...so helpful!

An inline option that worked for me, and was a bit simpler:

In my example the error was in bite 4, so that's what I used, but it could be on any bite: 1 - 7

Worksheets.Item(strSheet).Range(strColumn & strRow & ":" & strColumn & strRow).Errors(4).Ignore = True

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy