Excel validation range limits

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:

1. When saving as a standard Excel file (Office 2000 BIFF), a maximum of 1024 non-contiguous ranges that can have the same validation setting.

2. 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.

3. When saving as an Open Office XML (Office 2007 *.xlsx), there is a maximum of 511 non-contiguous ranges that can have the same val开发者_运维百科idation 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.

The workaround is to make the Validation settings for each range somehow different (change the error message, etc.) so Excel does not attempt to combine them. Solution tested in Office 2003 for all three file types, works as expected.