75MB Excel File - Can't Delete Empty Cells
 

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.

75MB Excel File - Can't Delete Empty Cells
 

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


"alandh" wrote:

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.

75MB Excel File - Can't Delete Empty Cells
 

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?


"Gary''s Student" wrote:

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


"alandh" wrote:

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.

75MB Excel File - Can't Delete Empty Cells
 

You may also wnat to check for Custom Cell Styles in the Styles section of
the Home Ribbon.

I had some converted Excel 2003 files that had over 100 extra custom cell
styles (apparently accumulated from every sheet ever inserted)
--
If this helps, please remember to click yes.


"Gary''s Student" wrote:

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


"alandh" wrote:

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.

75MB Excel File - Can't Delete Empty Cells
 

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."



"alandh" wrote:

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?


"Gary''s Student" wrote:

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


"alandh" wrote:

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.

75MB Excel File - Can't Delete Empty Cells
 

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



"alandh" wrote in message
...
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.

75MB Excel File - Can't Delete Empty Cells
 

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


"alandh" wrote:

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."



"alandh" wrote:

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?


"Gary''s Student" wrote:

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


"alandh" wrote:

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.