9

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 3

11

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
  • No button appears when you select a range, but the macro worked great. Many thanks.
    – elb98rm
    Apr 11, 2013 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, 2016 at 14:41
1

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!

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .