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
Display More
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.