Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
File Size Jump
So if I move the data to a new sheet - how do I make sure this doesn't happen
again? Should I discontinue using this code? I like only seeing cells that are being utilized... And yes, this sheet is using a lot of formulas and links, etc., and shifting it would take hours... "Dave Peterson" wrote: 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 . |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
File Size Jump
I am interested in knowing what criterion is used for the number you place
in U1 How do you decide to enter 42 or 978? Gord On Mon, 25 Jan 2010 15:26:01 -0800, New2Macros wrote: So if I move the data to a new sheet - how do I make sure this doesn't happen again? Should I discontinue using this code? I like only seeing cells that are being utilized... And yes, this sheet is using a lot of formulas and links, etc., and shifting it would take hours... "Dave Peterson" wrote: 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 . |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
File Size Jump
I wouldn't use that code.
New2Macros wrote: So if I move the data to a new sheet - how do I make sure this doesn't happen again? Should I discontinue using this code? I like only seeing cells that are being utilized... And yes, this sheet is using a lot of formulas and links, etc., and shifting it would take hours... "Dave Peterson" wrote: 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 . -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
File Size Jump
Well, we just thought it made the sheets look clean, and you couldn't enter
data in cells that weren't being utilized. We use this spreadsheet for our equipment list, so we enter a number in Q1 based off of how many devices will be listed on this sheet. All the sheets are linked to a "master sheet" which is what we print out, and you don't want someone to enter data on a line in the other tabs that isn't set to be printed. I hope that makes sense... "Dave Peterson" wrote: I wouldn't use that code. New2Macros wrote: So if I move the data to a new sheet - how do I make sure this doesn't happen again? Should I discontinue using this code? I like only seeing cells that are being utilized... And yes, this sheet is using a lot of formulas and links, etc., and shifting it would take hours... "Dave Peterson" wrote: 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 . -- Dave Peterson . |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
File Size Jump
I still wouldn't use the macro.
New2Macros wrote: Well, we just thought it made the sheets look clean, and you couldn't enter data in cells that weren't being utilized. We use this spreadsheet for our equipment list, so we enter a number in Q1 based off of how many devices will be listed on this sheet. All the sheets are linked to a "master sheet" which is what we print out, and you don't want someone to enter data on a line in the other tabs that isn't set to be printed. I hope that makes sense... "Dave Peterson" wrote: I wouldn't use that code. New2Macros wrote: So if I move the data to a new sheet - how do I make sure this doesn't happen again? Should I discontinue using this code? I like only seeing cells that are being utilized... And yes, this sheet is using a lot of formulas and links, etc., and shifting it would take hours... "Dave Peterson" wrote: 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 . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Size With Macros Has Increased From Its Origina Size | Excel Discussion (Misc queries) | |||
Size Jump | Excel Discussion (Misc queries) | |||
Excel 2003 default file location always jump to .....\Proof Help | Excel Discussion (Misc queries) | |||
Excel kept on asking to save file every time I jump from one sheet to another! | Excel Worksheet Functions | |||
jump to hyperlink, extract value at specific destination in file | Excel Worksheet Functions |