Excel last cell bug

Question

Having a very frustrating problem with Excel here - can't delete the last cell; it's locked at the last used column and row 1048576. Before you jump off to the common fixes, I've already tried every fix I could find on the internet including:

delete all unused rows, delete all unused columns
save, close, reopen
clear formatting in all unused rows & columns
various macro's referencing worksheet.UsedRange, UsedRange.Rows.Count, UsedRange(1), and so forth.
many permutations of the above in different orders, always followed by a save / re-open

This is occurring in both Excel 2007 and Excel 2013. Even when I delete EVERYTHING on the worksheet (i.e. select all, right click, delete), save / open, the last cell STILL remains referenced to the last row.

I can just copy and paste the active regions of the worksheet to a new sheet, but I've had this happen before and I'd like to find a complete fix for future reference (or know that this is indeed a bug and I'm not just going nuts here!)

Use this `Range("A" & Rows.Count).End(xlUp).Row` to get the last row. — Santosh, Oct 30 '13 at 04:41
I have a feeling that you are using `usedRange` to find the last cell. If yes, then please see [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — Siddharth Rout, Oct 30 '13 at 07:34

score 0 · Answer 1 · answered Oct 30 '13 at 10:06

I also had the situation a few times - where the worksheet was nearly empty, but for some strange reason, Excel stored all 1M rows - and none of the normal tricks worked.

What I end up doing in such a situation is to directly edit the .xlsx file:

Rename the .xlsx (or .xlsm) file to .zip and you'll see the content
Extract the file xl\worksheets\sheet1.xml (or whichever sheet is really large - note that the numbering might need be aligned with the numbering in Excel)
Edit the file with a potent editor. I can recommend UltraEdit, esp. as it has an "XML Convert to CR/LF" feature (under Format) which will format the XML to a readable format. (If it's 1M rows, this formatting might take a few minutes!)
In the file search for <row r="X" where X is the last row you want to keep. Delete everything from here until </sheetData> (usually in the third last row)
Save the file, place it back in the .zip and rename the .zip back to .xlsx/.xlsx

Done!

score 0 · Answer 2 · answered Oct 03 '19 at 16:28

(A day late and a dollar short)

I'm still using excel 2003 and have this problem. These macros delete the old sheet and add a new sheet. The first macro will truncate a sheet so the "Ctl-End Lastcell" is the last of the content. The second macro just deletes the sheet and adds a new sheet with the same name so the "Ctl-End Lastcell" is A1.

Sub ShTrunc() ' truncate active sheet to content only
  Dim sold$, snew$, rowz&, colz&, zRange As Range
  ' -- get old and new sheet names
  sold = ActiveSheet.Name ' old sheet name
  Sheets.Add
  snew = ActiveSheet.Name ' new name
  ' -- get the "true" last row and column
  ' based on http://www.rondebruin.nl/win/s9/win005.htm
  Sheets(sold).Activate
  Set zRange = Cells.Find("*", [a1], xlFormulas, xlPart, xlByRows, xlPrevious, False)
  If zRange Is Nothing Then rowz = 1 Else rowz = zRange.Row
  Set zRange = Cells.Find("*", [a1], xlFormulas, xlPart, xlByColumns, xlPrevious, False)
  If zRange Is Nothing Then colz = 1 Else colz = zRange.Column
  ' -- copy the content from old sheet, paste to new sheet
  Range(Cells(1, 1), Cells(rowz, colz)).Copy ' Sheets(snew).Cells(1, 1)
  Sheets(snew).Activate
  ActiveSheet.Paste
  ' -- delete old sheet and rename new to old
  Application.DisplayAlerts = False
  Sheets(sold).Delete
  Application.DisplayAlerts = True
  Sheets(snew).Name = sold ' rename to old name
  ' -- the following checks if the world works as it should
  If ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row <> rowz Then Stop
  If ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column <> colz Then Stop
End Sub

Sub ShDelAdd() ' delete and add active sheet (to CLEAR it)
  ' this is a simpler version of ShTrunc if you only want to clear a sheet
  Dim sold$, snew$
  sold = ActiveSheet.Name
  Application.DisplayAlerts = False
  Sheets(sold).Delete
  Application.DisplayAlerts = True
  Sheets.Add
  snew = ActiveSheet.Name ' new name
  Sheets(snew).Name = sold ' rename to old name
  ' -- the following checks if the world works as it should
  If ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row <> 1 Then Stop
  If ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column <> 1 Then Stop
End Sub

Excel last cell bug

2 Answers2