Excel 2007 BUG UsedRange/LastCell differences with Excel2003.

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

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
"keepITcool" <[email protected]> wrote in message
news:[email protected]...
|
| 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

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)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Rech wrote in <news:<[email protected]>

> 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