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.

migrated from stackoverflow.com Apr 11 '13 at 9:01

This question came from our site for professional and enthusiast programmers.


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
  • No button appears when you select a range, but the macro worked great. Many thanks. – elb98rm Apr 11 '13 at 14:21
  • The button appears at the first (top) cell in the range. The button is a small yellow circle containing an exclamation point. If you are at the bottom of a large range you may not see it. – KAE Jul 18 '16 at 14:41

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

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!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.