75MB Excel File - Can't Delete Empty Cells

[alandh]

I made a template for running some statistical data through. It ran slow
every time I tried saving and it wasn't until I tried e-mailing it that I
realized it was 75MB. It has 14 spreadsheets and all my data is contained
with in the first 20 columns and first 50 rows. the problem is that the rows
go down to ~65,000 and I can't seem to get rid of them. Selecting them alone
using ctl+shft+down makes the program not respond for a few minutes and when
I right click and select delete the program freezes altogether. I have tried
selecting smaller portions of the spreadsheets and it still doesn't seem to
lower the total amount of rows.

The spreadsheets were originally made in 2002 and now in 2007[Compatibility
Mode].

Any help would be much appreciated - especially considering I have people
waiting for this data, but cannot send it out.

 

[alandh]

Okay, I figured out how to run a Macro, but apparently there is an error in
the code you gave me. Its telling me that r.Clear is causing errors because
"Runtime error 1004: Cannot change merged part of a cell."

I'd like to think that I'm not that bad with computers - but I'm not a
programmer by any means. I just know how to do basic data input and run
formulas in excel files. What do I exactly do with that? How do I run it?

This may take a while to run:

Sub cleaner()
For Each r In ActiveSheet.UsedRange
If r.Value = "" Then
r.Clear
End If
Next
End Sub

but it should fix the problem.
--
Gary''s Student - gsnu200857

I made a template for running some statistical data through. It ran slow
every time I tried saving and it wasn't until I tried e-mailing it that I
realized it was 75MB. It has 14 spreadsheets and all my data is contained
with in the first 20 columns and first 50 rows. the problem is that the rows
go down to ~65,000 and I can't seem to get rid of them. Selecting them alone
using ctl+shft+down makes the program not respond for a few minutes and when
I right click and select delete the program freezes altogether. I have tried
selecting smaller portions of the spreadsheets and it still doesn't seem to
lower the total amount of rows.

The spreadsheets were originally made in 2002 and now in 2007[Compatibility
Mode].

Any help would be much appreciated - especially considering I have people
waiting for this data, but cannot send it out.

 

[Mike Middleton]

alandh -

As a first step, I suggest resetting the "used range" for each worksheet.

Debra Dalgeish has instructions at
http://www.contextures.com/xlfaqApp.html#Unused

The worksheet will always have 65,536 rows. You cannot change that (unless
you use Excel 2007). But you can change how many rows are used (or how many
rows Excel thinks are being used).

- Mike

http://www.MikeMiddleton.com

 

[Gary''s Student]

This macro will not work if any of the cells are Merged.
--
Gary''s Student - gsnu200857

Okay, I figured out how to run a Macro, but apparently there is an error in
the code you gave me. Its telling me that r.Clear is causing errors because
"Runtime error 1004: Cannot change merged part of a cell."

I'd like to think that I'm not that bad with computers - but I'm not a
programmer by any means. I just know how to do basic data input and run
formulas in excel files. What do I exactly do with that? How do I run it?

This may take a while to run:

Sub cleaner()
For Each r In ActiveSheet.UsedRange
If r.Value = "" Then
r.Clear
End If
Next
End Sub

but it should fix the problem.
--
Gary''s Student - gsnu200857


:

I made a template for running some statistical data through. It ran slow
every time I tried saving and it wasn't until I tried e-mailing it that I
realized it was 75MB. It has 14 spreadsheets and all my data is contained
with in the first 20 columns and first 50 rows. the problem is that the rows
go down to ~65,000 and I can't seem to get rid of them. Selecting them alone
using ctl+shft+down makes the program not respond for a few minutes and when
I right click and select delete the program freezes altogether. I have tried
selecting smaller portions of the spreadsheets and it still doesn't seem to
lower the total amount of rows.

The spreadsheets were originally made in 2002 and now in 2007[Compatibility
Mode].

Any help would be much appreciated - especially considering I have people
waiting for this data, but cannot send it out.