+ Reply to Thread
Results 1 to 2 of 2

Ignore Errors on multi-cell range without looping over cells

  1. #1
    Forum Contributor
    Join Date
    08-09-2005
    MS-Off Ver
    2003 & 2007
    Posts
    111

    Ignore Errors on multi-cell range without looping over cells

    I want to eliminate the little green triangles that warn the user about possible errors. I know I can accomplish this via VBA as follows:

    Please Login or Register  to view this content.

    My question is: how can I do this without looping over all cells in the range?

    Here's a way to do this in the user interface: I select a cell with the error triangle, then type CTRL+A, then I click the little yellow diamond with the exclamation point, then choose Ignore Error. Then the entire range is cleared of the green triangles. Unfortunately, the macro recorder doesn't record anything for this action.

    The reason I need this in one statement (or a few statements) rather than one per cell is I'm accessing Excel through a .NET to COM interop layer which slows things down by a factor of ~200. MyRange can contain hundreds of thousands of cells, so it's imperative to keep the number of individual calls as small as possible.

    I know it would be possible to turn off error checking globally:

    Please Login or Register  to view this content.

    But I think it's a bad idea to mess with a user's settings solely for the sake of viewing an individual document. The end point of my program is create an Excel workbook, after which the user can do whatever they want with the workbook (save or discard). So there would be no opportunity to turn error checking back on.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Ignore Errors on multi-cell range without looping over cells

    1) To turn off that option forever, go to Tools > Options > Error Checking > [ ] Numbers stored as Text and uncheck that.

    2) To make this occur on a single specific sheet, put this into your SHEET module:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1