12

I have an excel document, Office 2007, on a Windows 7 machine (if that part matters any, I'm not sure but just throwing it out there). It is a list of all employee phone numbers. If I need to generate a new page, I can click on page 2 and the table will automatically generate again.

The problem is, someone messed it up since it's on a network drive and now shows I have over 960,000 rows of data, when I really don't! I did CTRL+END to see if any data was in the last cell, so I cleared it out, deleted that row and column, but still didn't fix it. It almost seems like it duplicates itself after the deletion.

How can I fix this instead of recreating the entire document?

  • 1
    Have you tried to restore a backup? Also, you're going to have to provide more information about what the macro or VBA is doing to generate a new table. Can you post that information? – CharlieRB Jun 5 '12 at 14:00
  • @CharlieRB As I looked more at the document, I realized it wasn't generating a new table. It wasn't one that I originally created, it was just a border setting that looked like a table. I tried to restore a backup but apparently the problem happened before the last good backup of it. If I go to the very last row which is 1,048,576... when I try to delete that row, in place of it is the bordered cells like at the beginning of the document. – C-dizzle Jun 5 '12 at 17:29
  • @CharlieRB I ended up going back and re-creating the document from scratch, but it still leaves me wondering what keeps replacing empty cells with borders that I'm not putting there. – C-dizzle Jun 5 '12 at 17:31
  • If it is a shared file, you may have someone that thinks the way to format cells is to do whole columns at a time, rather than just what is needed. Or is this done if you are the only one accessing the file, now? – datatoo Jun 5 '12 at 18:12
13

Microsoft has an excellent support document called How to reset the last cell in Excel.

From that document:

The most common cause of the last cell being set outside the worksheet range that is currently in use is excessive formatting. When you format whole rows and columns, some types of formatting can cause the last cell to be set to a cell far below or to the right of the actual range that is in use.

Based on the comments below your question, it certainly seems like the border format that is applied to all rows is the culprit.

I've used the code from the add-in provided from the link above to reduce file sizes from multiple megabytes to a few hundred k.

  • Am using excel2010 and it does not work, simply gives the error "excel cannot complete this task with the available resources. choose less data or close other applications" – adolf garlic Jan 14 '16 at 14:55
  • The link above has been dead. @adolfgarlic try the VBA solution – phuclv Jul 25 '18 at 9:26
  • @adolfgarlic It doesn't work on the 32-bit version of Excel, but it does on the 64-bit version that doesn't enforce the same memory limitations. Alas, after changing my version from 32 to 64 bits, this solution runs - but does not solve the problem. – t0mgs Aug 16 '18 at 6:19
  • @phuclv Quite alive, actually. – t0mgs Aug 16 '18 at 6:19
0

Also check to see if there are any cells with comments where the comment box has been dragged down far away from the cell. You can have a cell in the second row of a spreadsheet with a comment whose comment box is near row 7000 and that will force excel to treat row 7000 as the spreadsheet's last row.

0

Like the commenter above - I had this same issue with having over a million extra rows in my excel doc, caused by comments ending up really far from the cell. This page has an easy VBA code you can use to clean up your document, then you can remove it and save the document without the code and the issue will be fixed. https://www.extendoffice.com/documents/excel/2252-excel-reset-comment-positions.html

  • Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. – Glorfindel Feb 22 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.