I am working with a large spreadsheet, typically about 2000 rows and
100 or more columns. On the far right, hidden, are a number of columns
that contain input data, with many blank cells. On the left is the
"user area", where formulas combine values from the input data into
user-friendly values.

The problem I'm having is that the formulas return #VALUE! when the
input cells are empty - and will remain so. Yes, I know I can turn
this warning off, but I don't want to, because it's a global setting
and both that user and other sheets I make need to have it on. So I
want to fix the problem.

I tried looping over the cells looking for ISERROR and emptying them
out, but this turned out to be VERY slow - about 25% of the time
needed to prepare the entire sheet! Then I tried copy/paste
SkipEmptyRows, but of course the rows aren't actually empty. Is there
a way to tell it to skip rows with errors? Or perhaps some other way
to fix them?

Maury

If you are using a macro to do this you can use if statements for the formula
to not include if the cell value is empty.

Or maybe you can use =iferror
--
Roland


"Maury Markowitz" wrote:

I am working with a large spreadsheet, typically about 2000 rows and
100 or more columns. On the far right, hidden, are a number of columns
that contain input data, with many blank cells. On the left is the
"user area", where formulas combine values from the input data into
user-friendly values.

The problem I'm having is that the formulas return #VALUE! when the
input cells are empty - and will remain so. Yes, I know I can turn
this warning off, but I don't want to, because it's a global setting
and both that user and other sheets I make need to have it on. So I
want to fix the problem.

I tried looping over the cells looking for ISERROR and emptying them
out, but this turned out to be VERY slow - about 25% of the time
needed to prepare the entire sheet! Then I tried copy/paste
SkipEmptyRows, but of course the rows aren't actually empty. Is there
a way to tell it to skip rows with errors? Or perhaps some other way
to fix them?

Maury

Both of those would work, but make the formula much more difficult to
read.

Is there some sort of "conditional find" that would allow me to find/
replace on the errors?

Maury