Ghost last cell used

Hi All,

I use Excel 2010. I have an workbook with several worksheets used as database. All rows and columns that are not used are hidden. When a new record is added, the first hidden row following the last record (used row) is unhidden.

After some use the last cell used is moved to a cell with no data sometimes more tha double the number of rows further than the actual last row. If I run MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address the response is way beyond the actual last cell used.

I have tried everything, (a) delete all rows up to and beyond the ghost last cell, and (b) delet the "contents" of all cells up to and beyond the ghost last cell, to no avail.

If I copy and paste the actual data to a new worksheet, the actual last cell used is given.

I assume a marker is put in the worksheet one or other way where the last used cell is.

My question is: How do I correct this marker without having to go through the schlep of copy and past to a new worksheet which creates a lot of additional work to format, reset cell addresses, etc?

The LastCell can be unreliable and is affected by new and deleted rows. Deleting blank rows beyond the last used row and saving the workbook usually resolves this.

I prefer to use a column where there is known to be data in all rows. For example:

Dim lLR As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row
Msgbox lLR

This doesn't rely on internal flags or counters and will return the last row used. Add 1 for the NEXT free row.

Regards, TMS

Hi Zagrijs,

This is interesting....!!
Not sure but it may be happening because of row insertion process which may move down the used rows / cells..

If possible, post the file for forum to have a quick look. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Excel often mistakes the last cell, after changes to a sheet

http://support.microsoft.com/kb/244435