Excel 2003 - deleting trailing empty rows

[ICTag]

Whenever a colleague updates a spreadsheet I've created it comes back
10MBytes large whereas it was originally only 50KBytes. It looks to me as if
the colleague accidentally adds tens or hundreds of thousands of empty rows.

How can I delete all rows below a certain row. Delete does not work. Copying
the real rows into a new worksheet loses the formatting. When I apply the
formatting from the big spreadsheet (paste formatting) I end up with hundreds
of thousands of rows again :-(

Any suggestions?
Tom

 

[Luke M]

Selecting all the rows below your actual data. Right-click, delete cells.
(this is different than simply pressing the delete key). Save your workbook.
This should reset the used range of your worksheet.

 

[ICTag]

Many thanks. This certainly helped.
There's no "delete cells" menu but the "delete" in the context menu reduced
the size from 8MB to 1.5MB. There are still over 65000 empty rows but when I
tried repeating the operation it didn't reduce the size any further.

 

[gls858]

Whenever a colleague updates a spreadsheet I've created it comes back
10MBytes large whereas it was originally only 50KBytes. It looks to me as if
the colleague accidentally adds tens or hundreds of thousands of empty rows.

How can I delete all rows below a certain row. Delete does not work. Copying
the real rows into a new worksheet loses the formatting. When I apply the
formatting from the big spreadsheet (paste formatting) I end up with hundreds
of thousands of rows again :-(

Any suggestions?
Tom

What is your colleague doing to update the sheet? I would suggest going
through his update process step by step (saving and checking size at
each step) to see where the extra rows are being created. If you can't
figure it out then post the process here and maybe someone can spot the
error.


gls858

 

[Shane Devenshire]

Hi,

1. Click the first row number you want removed
2. Press Shift+Ctrl+Down Arrow
3. Press Ctrl+- (control minus)

I would also consider doing this for columns, in which case step 2 would be
Ctrl+Shift+Right Arrow

All worksheets in 2003 and earlier contain 65,536 rows and 256 columns
whether they have anything in them or not.

The menu command for Deleting rows or columns is Edti, Delete (not Edit,
Clear,...)

 

[ICTag]

Thanks Shane.
That's a cool way of selecting the lines but it doesn't shrink after deleting.
I'll have to find our what he does.
Tom