Excel validation range limits
Posted
by richardtallent
on Stack Overflow
See other posts from Stack Overflow
or by richardtallent
Published on 2009-12-09T19:56:51Z
Indexed on
2010/04/10
9:33 UTC
Read the original article
Hit count: 321
When Excel saves a file, it attempts to combine identical Validation settings into a single rule with multiple ranges.
This creates one of three issues, depending on the file type you choose to save:
When saving as a standard Excel file (Office 2000 BIFF), a maximum of 1024 non-contiguous ranges that can have the same validation setting.
When saving as a SpreadsheetML (Office 2002/2003 XML) file, you are limited to the number of non-contiguous ranges that can be represented, comma-delimited in R1C1 format, in 1024 characters.
When saving as an Open Office XML (Office 2007 *.xlsx), there is a maximum of 511 non-contiguous ranges that can have the same validation setting. (I don't have Office 2007, I'm using the file converter for Office 2003).
Once you bust any of these limits, the remaining ranges with the same Validation settings have their Validation settings wiped. For (1) and (3), Excel warns you that it can't save all of the formatting, but for (2) it does not.
© Stack Overflow or respective owner
