14

In an Excel 2010 file, there are about 100 rows containing data, but the whole work sheet displays 1048576 blank rows (making the file about 2.5 mb). I need to delete blank rows after the data. But selecting the row and deleting it is doing nothing. How to delete these unwanted rows?

This is my excel file: http://www.mediafire.com/download.php?au957fcnh3odbcd

Can someone explain what is wrong with this file?

6
  • 1
    Any version of Excel will display any of the available blank rows and allow you to enter data. If you want data on rows 1 and 1,000,000 that is your choice. But rows 2 to 999,999 do not take up space until you use them. Do you have images within the workbook? What happens if you copy the 100 rows to a blank file? Jan 6, 2012 at 9:29
  • @Tony Thanks for the answer, Finally I've managed to copy data (with some formatting) to new file.
    – Nalaka526
    Jan 6, 2012 at 10:01
  • @Tony If you're interested just check the file I've attached. Thanks
    – Nalaka526
    Jan 6, 2012 at 10:04
  • 2
    Applying a format to an entire column will cause this to happen. Don't do it: ozgrid.com/Excel/ExcelSpreadsheetDesign.htm
    – Excellll
    Jan 6, 2012 at 14:21
  • 1
    @Nalaka526, please do not edit my post for such a minor change when it is still new, since I was still improving it
    – ronalchn
    Nov 3, 2012 at 11:34

10 Answers 10

10

There is definitely something wrong with your file. It might be easier to copy the 100 rows you want to keep to a new workbook file rather than try to delete a million rows.

A search for duplicates in the whole document found and removed over 1 million duplicates. So there are hidden characters or something occupying space in that workbook. That still didn't help because the save size after that went up to 35MB.

Solution: Once I copied the rows you have populated to a new workbook, the saved file size is only 10K.

1
  • 1
    this is not an answer/solution, is just a workaround. Can an erroneously-1048576-rows-long file be fixed, without copying data to a new file?
    – jumpjack
    Feb 26, 2021 at 10:17
6

The commenter on Robert's post is right on target. After deleting the unwanted rows you need to save the workbook, close, and then re-open it.

Microsoft outlines the steps in their "How to reset the last cell in Excel" article. They also mention the Save step, but I wish they'd give it more emphasis. Until you save and re-open the workbook, it doesn't look like your deleting has done anything!

Copying the good rows to a new workbook is also a good solution, especially if you don't have any macros or formulas with complicated cell references to worry about.

1
  • "... if you don't have any macos ..." ... who doesn't have macros? Everyone should because they make life so much easier and introduce anyone who has never programmed to that art.
    – DSlomer64
    Jan 2, 2019 at 12:49
4

Select the rows you want to delete. If you have Office 2003: http://www.mrexcel.com/archive/Edit/4259.html

2007 and later: On the Ribbon, under the "Home" Tab, under the "Cells" Group and "Delete" Button (block called cells), select the small arrow under it then select "Delete Sheet Rows". Save file and Reopen File.

4
  • 1
    This did not fix the issue. :(
    – Nalaka526
    Jan 6, 2012 at 8:44
  • What is not working? Which version of Excel do you have?
    – Robert
    Jan 6, 2012 at 9:01
  • 1
    In Excel 2013 I was able to do the above. Although I got some grammatically-incorrect error-message along the lines of "Operation cannot be completed with available resources, cancel or select less data" I was then prompted to either "Cancel" or "Continue without undo". Selecting the latter and then saving seemed to fix the problem of Excel thinking my working space was 1048579 rows tall. Feb 13, 2016 at 6:35
  • this way you "delete" just data (of already empty rows), not rows; Excel keeps thiking all rows are filled with data.
    – jumpjack
    Feb 26, 2021 at 10:19
1

Another cause can be that one or several columns of the worksheet are referenced in some formula in another worksheet without specifying the number of rows.

0

Best option is to use ctrl+shift + downarrow to hide unwanted rows and ctrl+shift + right arrow to hide unwanteed columns

1
  • No. Which column or row should be selected? There's more than one row, more than one column.
    – DSlomer64
    Jan 2, 2019 at 12:50
0

I had this same issue and found a way to fix that worked on a few occasions for me.

Make sure you don't have any formatting going down those columns (e.g., cell borders applied to an entire column). Grab the scroll bar and drag it all the way to the bottom. Select the bottom row (1048579, I believe) and a bunch above it, about 20-30, whatever is visible on the screen. Right-click the row header area and click Delete. Drag the scroll bar back to the top and select a cell where your data is. Select a different worksheet, then go back. It should be fixed.

0

How to delete unwanted rows and columns in an Excel worksheet

(It won't take as long as a first look might suggest and it's safe!)

There are two sheets referred to in the procedure below.

Let's call your original sheet YourOriginalSheet (it represents the actual name of your original sheet).

Let's call the other sheet ShortSheet, which will contain a copy of only relevant cells.

(1) In YourOriginalSheet, Select and Copy the range of relevant cells (don't paste anywhere yet).

(2) Add a new sheet named ShortSheet, put the cursor into the cell representing the upper-left-hand corner of the range being copied (probably A1), and paste like so:

(2a) Paste Special... > Formulas [right-click the upper-left-hand corner cell]

(2b) Paste Special... > Values

(2c) Paste Special... > Formats

(2d) Paste Special... > Column Widths

(2e) Include other Paste Special options that you think might help make the sheet look better.

(3) Press Ctrl-Shift-End to find the lower-right-hand corner of ShortSheet to make sure it contains all relevant data.

(4) Save the workbook. (Save As ... a new file if you want a backup.)

(5) Delete YourOriginalSheet (The relevant data is currently saved in ShortSheet.)

(6) Rename ShortSheet to the actual name of your original sheet`.

Nothing has changed except to make the workbook a lot smaller, so all macros, external references to this worksheet, etc. should still work.


Here is a macro to accomplish the steps in (2).

Sub pasteSpecialAll()
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Before using the macro, Select and Copy the relevant range, as was done in step (1).

I saved my macro in my Personal.xlsb workbook for future use everywhere and assigned keypress Ctrl + Shift + V to it.

0

I found solution,

1) You need to delete anything at these empty rows, so click on first empy row (after all your data) and then click CTRL+Shift and arrow down. Thats how you select everything to the bottom.

2) On HOME panel at EDITING click Clear>Clear all

3) then click on style: Normal (without this it didint work for me)

4) and then you need to turn on this add-on(tutorial copied from ms support):

Click File > Options > Add-Ins.

Make sure COM Add-ins is selected in the Manage box, and click Go.
Manage COM Add-ins

In the COM Add-Ins box, check Inquire, and then click OK.
The Inquire tab should now be visible in the ribbon.

Important: You may want to make a backup copy of your file before cleaning the excess cell formatting, because there are certain cases where this process may make your file increase in size, and there is no way to undo the change.

To remove the excess formatting in the current worksheet, do the following:

On the Inquire tab, click Clean Excess Cell Formatting.

https://support.office.com/en-us/article/clean-excess-cell-formatting-on-a-worksheet-e744c248-6925-4e77-9d49-4874f7474738

This should clean your extra rows.

0

The secret is to delete the rows, save the spreadsheet and close. When you reopen the file will be smaller. In my case I had issues with several worksheets in the workbook.

1
  • This way I passed from 10 MB to 2 MB. But manually copying data to a new empty workbook, I get a 20 KB file!!!
    – jumpjack
    Feb 26, 2021 at 10:28
0

@Excellll was right. Maybe the problem is the formats. Clearing formats is the easy way. img


If you want to inspect the real problem, see xml contents of the sheet.

$ mkdir big
$ unzip big.xlsx -d big
$ tail -c 2048 big/worksheets/sheet1.xml
...<row r="1048576" spans="22:71" x14ac:dyDescent="0.25"><c r="V1048576" s="16"/></row></sheetData>...

In the above example, you should inspect the column V.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .