Microsoft Excel – Why Does My Document Have 960,000 Empty Rows?

microsoft excelmicrosoft-excel-2007windows 7

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?

Best Answer

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.

Related Solutions

Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.

Sub NewCF()
    Range("B1:P1").Copy
    For Each r In Selection.Rows
        r.PasteSpecial (xlPasteFormats)
    Next r
    Application.CutCopyMode = False
End Sub

To use, highlight the un-formatted rows in your dataset (in my case, B2:P300) and then run the macro. In the example below, note that the max numbers in the first two rows are 5 and 15, respectively; both cells are dark red.

I'm sure there's a faster solution than this, though.

enter image description here

Excel – How to embed the entire Excel workbook in an MS Word 2010 document

Sorry this was not answered sooner. If you still need to know how to do this, here is what I do.

In Word, select the Insert ribbon, choose Object. On the Object window (screen shot below), select the Create From File tab. Browse to find the file you want to imbed. Then check the box for Display As Icon. Click OK and it will place an in on your page.

enter image description here

Best Answer

Related Solutions

Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

Excel – How to embed the entire Excel workbook in an MS Word 2010 document

Related Question