Why does my excel document have 960,000 empty rows?

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?

C-dizzle

Posted 2012-06-05T13:28:48.977

Reputation: 1 856

1Have 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 – 2012-06-05T14:00:23.773

@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 – 2012-06-05T17:29:53.467

@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 – 2012-06-05T17:31:12.403

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 – 2012-06-05T18:12:37.753

Answers

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.

Jon Crowell

Posted 2012-06-05T13:28:48.977

Reputation: 1 864

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 – 2016-01-14T14:55:01.237

The link above has been dead. @adolfgarlic try the VBA solution

– phuclv – 2018-07-25T09:26:10.907

@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 – 2018-08-16T06:19:29.467

@phuclv Quite alive, actually. – t0mgs – 2018-08-16T06:19:51.290

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

Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

Click Insert > Module, and paste the following code in the Module Window.

VBA code: Reset all comment positions in active worksheet

1 Sub ResetComments()
2 'Update 20141110
3 Dim pComment As Comment
4 For Each pComment In Application.ActiveSheet.Comments
5   pComment.Shape.Top = pComment.Parent.Top + 5
6   pComment.Shape.Left = pComment.Parent.Offset(0, 1).Left + 5
7 Next
8 End Sub

Then press F5 key to run this code, and all the comments positions in the active worksheet have been reset at once.

Natalie

Posted 2012-06-05T13:28:48.977

Reputation: 1

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.

ivanatpr

Posted 2012-06-05T13:28:48.977

Reputation: 738