SpecialCells(xlCellTypeConstants) reports that *all cells* are
occupied by constants during the Workbook_BeforeSave event.

Try this:

-- In Excel 2007, create a new workbook, and save it as a macro-
enabled workbook.
-- Create a standard module, and enter this:

Sub test()

Debug.Print "Before save:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Add ress
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Cou nt
Debug.Print
ThisWorkbook.Save
Debug.Print "After save:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Add ress
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Cou nt
Debug.Print

End Sub

-- In the Workbook_BeforeSave event, enter this:

Debug.Print "During Workbook_BeforeSave Event:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Add ress
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Cou nt
Debug.Print

-- To avoid getting a runtime error while keeping the test simple,
enter any data in any one cell in Sheet1. I entered text in cell D7.


-- Run the test macro. Here's what I get, in compatibility mode:

Before save:
$D$7
1

During Workbook_BeforeSave Event:
$1:$65536
16777216

After save:
$D$7
1


What's up?


Again, that's in compatibility mode. If I close the workbook and
reopen it to get into normal Excel 2007 mode, I get this, and then an
overflow error:

Before save:
$D$7
1

During Workbook_BeforeSave Event:
$1:$1048576


I guess that's because Count is trying to return a Long, and the Count
would be 16,384 x 1,048,576 = 17,179,869,184; okay for a Double but
too big for a Long. I get the same overflow error even if I wrap it in
CDbl.

If I narrow down the range of cells (still in normal Excel 2007 mode)
to a single column to avoid the overflow error, like this:

Debug.Print
Sheet1.Columns(4).SpecialCells(xlCellTypeConstants ).Address
Debug.Print Sheet1.Columns(4).SpecialCells(xlCellTypeConstants ).Count

Then I get this:

Before save:
$D$7
1

During Workbook_BeforeSave Event:
$D:$D
1048576

After save:
$D$7
1


So -- why are all cells considered to be occupied by constants during
the Before_Save event?

BTW, the problem does not occur with SpecialCells(xlCellTypeFormulas);
only with SpecialCells(xlCellTypeConstants).


Thanks,

Greg