Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete cells column. Delete empty cells | Excel Worksheet Functions | |||
How do I delete rows where cells are empty | Excel Discussion (Misc queries) | |||
delete all empty cells in a range | Excel Worksheet Functions | |||
Delete Empty Cells in a Macro | Excel Discussion (Misc queries) | |||
delete rows with empty cells | Excel Discussion (Misc queries) |