I was having an issue with a worksheet that I use for temporary storage during the running of my code. Prior to use, I would select all of the data rows and clear them out then save the workbook to reset the internal reference to lastCell. For some reason, it appeard to be having difficulty so I started searching here and on MSDN for a sollution.
Everything I found said the same thing that I was doing except for one reference on MSDN. You can read the excerpt HERE (Chapter 5: Using Ranges) and if you search down to where they talk about the SpecialCells Method, it indicates that you can execute ActiveSheet.UsedRange to reset the lastCell reference.
The chapter is an excerpt for Excel 2002 VBA and I am running 2003 but, it did seem to work. Here is the code in case you'd rather not get it from the site:
Sub DeleteUnusedFormats() Dim lLastRow As Long, lLastColumn As Long Dim lRealLastRow As Long, lRealLastColumn As Long With Range("A1").SpecialCells(xlCellTypeLastCell) lLastRow = .Row lLastColumn = .Column End With lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column If lRealLastRow < lLastRow Then Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete End If If lRealLastColumn < lLastColumn Then Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)).EntireColumn.Delete End If ActiveSheet.UsedRange 'Resets LastCell End Sub
Given there were quite a few threads that I found during my search, I thought I would post this as maybe a help to others who may run into the same problem. Again though, it appears it may have changed between 2002 to 2003 but, it's worth a try. Even if the reset doesn't work, the code to find the actual last cell may be valuable to some.