Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

EDIT:

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

can someone explain what is wrong with this file??

share|improve this question
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? –  Tony Dallimore Jan 6 '12 at 9:29
    
@Tony Thanks for the answer, Finally I've managed to copy data (with some formatting) to new file. –  Nalaka526 Jan 6 '12 at 10:01
    
@Tony If you're interested just check the file I've attached. Thanks –  Nalaka526 Jan 6 '12 at 10:04
    
Happy that I could be of some help. I have copied your file to my computer. However, my ex-employer and most of my clients still use Excel 2003 so that is what I have on my main computer. I will try your file on a different computer tomorrow and get back if I discover anything. However, most Office products sometimes get themselves into a muddle and your option is to extract the data to a empty file. –  Tony Dallimore Jan 6 '12 at 11:25
    
Applying a format to an entire column will cause this to happen. Don't do it: ozgrid.com/Excel/ExcelSpreadsheetDesign.htm –  Excellll Jan 6 '12 at 14:21
show 1 more comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
add comment

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

2007 and later: in the ribbon on start, there is the button delete (block called cells), click the small arrow under it and choose delete rows.

share|improve this answer
    
This did not fix the issue. :( –  Nalaka526 Jan 6 '12 at 8:44
    
What is not working? Which version of Excel do you have? –  Robert Jan 6 '12 at 9:01
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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