too many lines

B

Banjoko

Hi everyone
I have a problem and i hope you can me help. I have an Excel sheet
that is about 20 MB. In my opinion it is so large because it shows too
many lines, about 18k or so, although I only use 100. How can I delete
these??? I have already tried it with deleting the lines, doesnt
work.
Thanks
 
B

Bob Phillips

You cannot reduce a worksheet to less than 65K rows, as that is how many
Excel has. When you delete rows, Excel creates new, empty rows to take there
place. It shouldn't affect the workbook size though, as Excel does not store
all those empty cells, only the data contained in them.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Banjoko

Sorry that is not true.
Excel saves a lot of stuff including what you did to the cells.
and i dont want to delete all 65k lines. on a normal sheet Excel shows
you only as much as you have used (sure you can scroll down but it
saves only the one you use). In my case I can for examble press
Strg+End and i jump ti row 18k. That is not normal and i dont know how
to fix it.

By the way, w´hen i copy my Data to a new sheet, it is about 1 MB...
 
B

Bob Phillips

What you are referring to is a bug in Excel's ability to set the last used
range. If you use some cells at the end of the data and then clear them out,
Excel doesn't reset the lastusedrange pointer, and so thinks those cells are
still used, and saves them. If you do this a lot, you need to delete those
rows and then save the file (it is all explained here
http://www.contextures.com/xlfaqApp.html#Unused). That's a hell of a lot of
rows though to swell it by 19Mb, and doesn't alter the validity of my
initial statement.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
K

kletcho

Here are a couple of ideas to check:

1. Bob is right about used ranges. Try the link to the VBA code that
he provided, its a good one (except for handling protected sheets and
handling any shapes).
2. Do you have a lot of pivot tables? Each pivot table costs about 2
MB give or take (unless you base them on other pivot tables)
3. Do you have a lot of graphics inserted (or a few high quality
graphics that you could reduce the quality on?)
4. Sometimes a file also blossoms in size if it is a complex
spreadsheet or a corrupted spreadsheet. Try moving the whole
spreadsheet into a new file by using the "move or copy sheets" function
found by right clicking on a sheet name.
 
P

Pete_UK

Another reason for large increases in memory is named ranges - check
out what is defined in your sheet by Insert | Name | Define and
scrolling through the list. Delete any that are no longer used, and
ensure that you adjust the range (shown in the bottom panel - "Refers
to") for any that are obviously too large.

Hope this helps.

Pete
 
P

Patricia Shannon

If deleting rows (using the menu edit/delete) doesn't help in where Ctrl+End
goes, save the file to itself with save as.
 
Top