Excel 2007 BUG UsedRange/LastCell differences with Excel2003.

[keepITcool]

In Excel 2003 the LastCell is reset when UsedRange method is called.
This does not happen in Excel2007

Try following macro in Excel2003 and Excel2007

Sub LastCellBug()

Cells(1000, 100).Value = 1
Cells(1000, 100).Clear
Debug.Print "Before Save"
Debug.Print ActiveSheet.UsedRange.Address
Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Address
ActiveWorkbook.SaveAs "c:\temp.xls"
Debug.Print "After Save"
Debug.Print ActiveSheet.UsedRange.Address
Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Address

End Sub

 

[Jim Rech]

Too bad. I use that even though a save resets the used range too. I doubt
it's a bug in the MS sense though. Have you reset your default file save as
file type? I had to change to this to not error:

ActiveWorkbook.SaveAs "c:\temp.xls", xlExcel8

--
Jim
|
| In Excel 2003 the LastCell is reset when UsedRange method is called.
| This does not happen in Excel2007
|
| Try following macro in Excel2003 and Excel2007
|
| Sub LastCellBug()
|
| Cells(1000, 100).Value = 1
| Cells(1000, 100).Clear
| Debug.Print "Before Save"
| Debug.Print ActiveSheet.UsedRange.Address
| Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Address
| ActiveWorkbook.SaveAs "c:\temp.xls"
| Debug.Print "After Save"
| Debug.Print ActiveSheet.UsedRange.Address
| Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Address
|
| End Sub
|
|
| --
| keepITcool
|| www.XLsupport.com | keepITcool chello nl | amsterdam

 

[keepITcool]

I did a few checks:

Default format (Excel Options): Excel Workbook

Activeworkbook.SaveAs "c:\temp.xls"
(not specifying fileformat, specifying 'wrong' extension)
in Excel2007 saves with xls extension in xlsx fileformat without
warnings. (same if you use .txt extension)

Activeworkbook.SaveAs "c:\test" Saves C:\Test.xlsx

the xls will open without errors in Excel2007,
but must be renamed to .xlsx to open in Excel2003 (with converter)