If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
File Size Jump
I can't figure out why my Excel file size is jumping from 550kb to 35mb. I
am running some formulas to copy data, and some special links from other pages, as well as scripts which hides rows... I tried deleting extra unhidden rows, and got nothing. Any ideas? |
#2
|
|||
|
|||
File Size Jump
After deleting the extra rows and columns you must SAVE the workbook to
effect changes in used range. As far as the size jump..............change the macro code to copy data etc. to a defined rather. Sounds like you are copying to a greater range than you need. Gord Dibben MS Excel MVP On Fri, 22 Jan 2010 09:06:01 -0800, New2Macros wrote: I can't figure out why my Excel file size is jumping from 550kb to 35mb. I am running some formulas to copy data, and some special links from other pages, as well as scripts which hides rows... I tried deleting extra unhidden rows, and got nothing. Any ideas? |
#3
|
|||
|
|||
File Size Jump
Should read defined range
On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. |
#4
|
|||
|
|||
File Size Jump
Still can't find it. I only have a string in Visual Basic - I tried to stay
away from macros. All that string is doing is cutting off rows based on a certain cell's value. Thanks "Gord Dibben" wrote: Should read defined range On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. . |
#5
|
|||
|
|||
File Size Jump
I have not yet seen confirmation that you saved the workbook after deleting
unused rows and columns on all worksheets. I cannot see the "string in Visual Basic" so not easy to trouble-shoot that aspect. Gord On Fri, 22 Jan 2010 12:12:07 -0800, New2Macros wrote: Still can't find it. I only have a string in Visual Basic - I tried to stay away from macros. All that string is doing is cutting off rows based on a certain cell's value. Thanks "Gord Dibben" wrote: Should read defined range On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. . |
#6
|
|||
|
|||
File Size Jump
I have had to save it a lot and keep backups to fall back on if it suddenly
expands in size. I deleted rows and colums that were not being hidden by Visual Basic. However, nothing has been changed in Visual Basic for a year, and it has been working fine until now. Here is the string I'm using to hide rows that are being unused: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$U$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(3 + 2 * Range("U1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub I just plug a number into cell U1, and it hides the correct cells. Thanks "Gord Dibben" wrote: I have not yet seen confirmation that you saved the workbook after deleting unused rows and columns on all worksheets. I cannot see the "string in Visual Basic" so not easy to trouble-shoot that aspect. Gord On Fri, 22 Jan 2010 12:12:07 -0800, New2Macros wrote: Still can't find it. I only have a string in Visual Basic - I tried to stay away from macros. All that string is doing is cutting off rows based on a certain cell's value. Thanks "Gord Dibben" wrote: Should read defined range On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. . . |
#7
|
|||
|
|||
File Size Jump
I tested your code on a new completely blank worksheet.
Before adding your code saved workbook size was 9kb Changed the number in U1 several times, saving workbook in between changes. Workbook grew to 2.8MB after a few saves. I unhid all rows and deleted all rows. Removed code from worksheet. Saved workbook and is now 2.50MB Cannot get it lower by any means available including Debra's reset usedrange code. Can anyone else see what's going on? I'm at a loss. Gord On Fri, 22 Jan 2010 15:06:01 -0800, New2Macros wrote: I have had to save it a lot and keep backups to fall back on if it suddenly expands in size. I deleted rows and colums that were not being hidden by Visual Basic. However, nothing has been changed in Visual Basic for a year, and it has been working fine until now. Here is the string I'm using to hide rows that are being unused: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$U$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(3 + 2 * Range("U1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub I just plug a number into cell U1, and it hides the correct cells. Thanks "Gord Dibben" wrote: I have not yet seen confirmation that you saved the workbook after deleting unused rows and columns on all worksheets. I cannot see the "string in Visual Basic" so not easy to trouble-shoot that aspect. Gord On Fri, 22 Jan 2010 12:12:07 -0800, New2Macros wrote: Still can't find it. I only have a string in Visual Basic - I tried to stay away from macros. All that string is doing is cutting off rows based on a certain cell's value. Thanks "Gord Dibben" wrote: Should read defined range On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. . . |
#8
|
|||
|
|||
File Size Jump
A longgggg time ago (xl97???), I wanted to hide columns that weren't used. So I
did the same kind of thing. I'd hide/unhide columns like that code. And each time the code ran (IIRC), the used range was changed. I didn't test the code, but it could be doing the same with the rows. (Adding a "msgbox me.usedrange.address" may show what's changing.) I discarded my routine after noticing this. It wasn't worth it. Gord Dibben wrote: I tested your code on a new completely blank worksheet. Before adding your code saved workbook size was 9kb Changed the number in U1 several times, saving workbook in between changes. Workbook grew to 2.8MB after a few saves. I unhid all rows and deleted all rows. Removed code from worksheet. Saved workbook and is now 2.50MB Cannot get it lower by any means available including Debra's reset usedrange code. Can anyone else see what's going on? I'm at a loss. Gord On Fri, 22 Jan 2010 15:06:01 -0800, New2Macros wrote: I have had to save it a lot and keep backups to fall back on if it suddenly expands in size. I deleted rows and colums that were not being hidden by Visual Basic. However, nothing has been changed in Visual Basic for a year, and it has been working fine until now. Here is the string I'm using to hide rows that are being unused: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$U$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(3 + 2 * Range("U1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub I just plug a number into cell U1, and it hides the correct cells. Thanks "Gord Dibben" wrote: I have not yet seen confirmation that you saved the workbook after deleting unused rows and columns on all worksheets. I cannot see the "string in Visual Basic" so not easy to trouble-shoot that aspect. Gord On Fri, 22 Jan 2010 12:12:07 -0800, New2Macros wrote: Still can't find it. I only have a string in Visual Basic - I tried to stay away from macros. All that string is doing is cutting off rows based on a certain cell's value. Thanks "Gord Dibben" wrote: Should read defined range On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. . . -- Dave Peterson |
#9
|
|||
|
|||
File Size Jump
That's the problem Dave.
The usedrange is continually being increased. But all efforts to reset usedrange using conventional means has failed. CTRL + End still goes to A1048576 Gord On Fri, 22 Jan 2010 19:09:39 -0600, Dave Peterson wrote: A longgggg time ago (xl97???), I wanted to hide columns that weren't used. So I did the same kind of thing. I'd hide/unhide columns like that code. And each time the code ran (IIRC), the used range was changed. I didn't test the code, but it could be doing the same with the rows. (Adding a "msgbox me.usedrange.address" may show what's changing.) I discarded my routine after noticing this. It wasn't worth it. Gord Dibben wrote: I tested your code on a new completely blank worksheet. Before adding your code saved workbook size was 9kb Changed the number in U1 several times, saving workbook in between changes. Workbook grew to 2.8MB after a few saves. I unhid all rows and deleted all rows. Removed code from worksheet. Saved workbook and is now 2.50MB Cannot get it lower by any means available including Debra's reset usedrange code. Can anyone else see what's going on? I'm at a loss. Gord On Fri, 22 Jan 2010 15:06:01 -0800, New2Macros wrote: I have had to save it a lot and keep backups to fall back on if it suddenly expands in size. I deleted rows and colums that were not being hidden by Visual Basic. However, nothing has been changed in Visual Basic for a year, and it has been working fine until now. Here is the string I'm using to hide rows that are being unused: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$U$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(3 + 2 * Range("U1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub I just plug a number into cell U1, and it hides the correct cells. Thanks "Gord Dibben" wrote: I have not yet seen confirmation that you saved the workbook after deleting unused rows and columns on all worksheets. I cannot see the "string in Visual Basic" so not easy to trouble-shoot that aspect. Gord On Fri, 22 Jan 2010 12:12:07 -0800, New2Macros wrote: Still can't find it. I only have a string in Visual Basic - I tried to stay away from macros. All that string is doing is cutting off rows based on a certain cell's value. Thanks "Gord Dibben" wrote: Should read defined range On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. . . |
#10
|
|||
|
|||
File Size Jump
Coincidentally, yesterday a friend sent me a (xl2003) where I had the same
problem resetting the usedrange. I tried using macros and manually and it just wouldn't let go of that last used cell. I removed formatting, filters. Deleted rows and columns and it all failed. I closed excel and reopened and the the last cell was reset--a hangover from earlier versions???? But when I have those hard-to-fix worksheets, I'll sometimes just give up and move/copy the data/formulas to a new sheet and delete the old one. That usually works--but it can be painful if there are lots of things to restore (names/headers/filters/...). Gord Dibben wrote: That's the problem Dave. The usedrange is continually being increased. But all efforts to reset usedrange using conventional means has failed. CTRL + End still goes to A1048576 Gord On Fri, 22 Jan 2010 19:09:39 -0600, Dave Peterson wrote: A longgggg time ago (xl97???), I wanted to hide columns that weren't used. So I did the same kind of thing. I'd hide/unhide columns like that code. And each time the code ran (IIRC), the used range was changed. I didn't test the code, but it could be doing the same with the rows. (Adding a "msgbox me.usedrange.address" may show what's changing.) I discarded my routine after noticing this. It wasn't worth it. Gord Dibben wrote: I tested your code on a new completely blank worksheet. Before adding your code saved workbook size was 9kb Changed the number in U1 several times, saving workbook in between changes. Workbook grew to 2.8MB after a few saves. I unhid all rows and deleted all rows. Removed code from worksheet. Saved workbook and is now 2.50MB Cannot get it lower by any means available including Debra's reset usedrange code. Can anyone else see what's going on? I'm at a loss. Gord On Fri, 22 Jan 2010 15:06:01 -0800, New2Macros wrote: I have had to save it a lot and keep backups to fall back on if it suddenly expands in size. I deleted rows and colums that were not being hidden by Visual Basic. However, nothing has been changed in Visual Basic for a year, and it has been working fine until now. Here is the string I'm using to hide rows that are being unused: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$U$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(3 + 2 * Range("U1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub I just plug a number into cell U1, and it hides the correct cells. Thanks "Gord Dibben" wrote: I have not yet seen confirmation that you saved the workbook after deleting unused rows and columns on all worksheets. I cannot see the "string in Visual Basic" so not easy to trouble-shoot that aspect. Gord On Fri, 22 Jan 2010 12:12:07 -0800, New2Macros wrote: Still can't find it. I only have a string in Visual Basic - I tried to stay away from macros. All that string is doing is cutting off rows based on a certain cell's value. Thanks "Gord Dibben" wrote: Should read defined range On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben gorddibbATshawDOTca wrote: As far as the size jump..............change the macro code to copy data etc. to a defined rather. . . -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|