Hi,

One of our users sent me an Excel file of 6 MiB.
It has 7 worksheets. Most of them have <100 rows and AH columns, one
sheet has 13160 rows and AH columns.
The large sheet has autofilter enabled, but no actual filtering is
done. (yet)
4 columns have validation: they allow a list of values specified in a
range somewhere else in the sheet.
There is also conditional formatting.
It takes 30 seconds to calculate the sheet, however there are no real
calculations, just a few concatenated string.
My first impression is that this is yet another example of Excel
(ab)used as a database.

The problem:
When the user tries to save (or save as), he gets the following
message:

Excel could not save all the data and formatting you recently added to
<filename.
* To return to your workbook without saving click Cancel.
* If you want to continue saving changes without repairing the
problem, click OK.
[OK] [Cancel] [Help]

Help gives a blank help page.

It appears the last added (or copypasted) rows in the sheet have lost
their validation. I can add validation again by copy - pastespecial -
validation, but Excel won't save that change.

I have found a KB with the same error message:
http://support.microsoft.com/kb/215783
This talks about more than 2050 rows of conditional formatting. It is
possible that there are more than 2050 rows with conditional
formatting, but this does not explain why the data validation is not
saved.

I found another KB:
http://support.microsoft.com/default...b;EN-US;213904
It talks about more than 4000 different combinations of formatting.
I'm not sure, but I don't think there are more than 4000 different
combinations. Anyway, data validation is not the same as formatting,
so I don't see this as relevant either.

I have removed all conditional formatting from the sheet, and I have
set most of the formatting (font, border, background,...) to the
default values. I still get the error.

What is going on and how can I solve this?

--
Amedee

On 1 aug, 14:33, Amedee Van Gasse wrote:

I forgot: Excel 2003. :-)

I think I found it.
I got a clue from this post:
http://groups.google.be/group/micros...d50703f0a298f7

I copied a cell in row 2, and pasted data validation in that column
for rows 2 - 13k-something. That makes it one continuous range with
validation. I repeated for 3 other columns with validation, who happen
to be next to each other.
After that I was able to save without any error.

I would like to say thank you to Debra Dalgleish.

--
Amedee