Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
I added some code to a worksheet, a macro and a list box and it increased the
size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
lines of code are small
what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
I did some investigation and it is a new worksheet I added that is causing
the size increase, strangely tho the worksheets has 1 list box and 1 column of data which is only 55 names I Exported the offending sheet to a new workbook and deleted everything from it, all the code everything this one sheet is increasing the size of the workbook by over 3.6 MB Anyone got any ideas Thanks "Vacation's Over" wrote: lines of code are small what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
On the sheet, do edit=Goto=special and select Last Cell. Do you end up
where you expected or are you way past where your data ends. If you ended up well past where your data ends, then see Debra Dalgleish's site for some hints: http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tom Ogilvy "Nigel" wrote in message ... I did some investigation and it is a new worksheet I added that is causing the size increase, strangely tho the worksheets has 1 list box and 1 column of data which is only 55 names I Exported the offending sheet to a new workbook and deleted everything from it, all the code everything this one sheet is increasing the size of the workbook by over 3.6 MB Anyone got any ideas Thanks "Vacation's Over" wrote: lines of code are small what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
No nothing special there, If I export to a new workbook it is 3.6MB, just wierd
"Tom Ogilvy" wrote: On the sheet, do edit=Goto=special and select Last Cell. Do you end up where you expected or are you way past where your data ends. If you ended up well past where your data ends, then see Debra Dalgleish's site for some hints: http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tom Ogilvy "Nigel" wrote in message ... I did some investigation and it is a new worksheet I added that is causing the size increase, strangely tho the worksheets has 1 list box and 1 column of data which is only 55 names I Exported the offending sheet to a new workbook and deleted everything from it, all the code everything this one sheet is increasing the size of the workbook by over 3.6 MB Anyone got any ideas Thanks "Vacation's Over" wrote: lines of code are small what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
Nigel,
Press Ctrl + End in the worksheet, this would take you to the Last Used Row. Though you may not see any data content there, possibilities are that something was typed there and removed. This is a behaviour we've come across when users press ctrl + downarrow and accidently type content in the 65536th row and delete the data but end up with a large file size. check the formats of the last row and also see if there are any space entered in a cell. or just delete the last row. Arun |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
OK I think I have the issue, I am moving\Copying the sheet to a new workbook,
If I just copy the data and paste into the new sheet it is much smaller, My dilema now is I need to have the combo box created on the new shet and the code added behind the scene The rows for the combo box are in A1 to A 50 and the code behind the combon box is NewSheet = Range("B1") Sheets(NewSheet).Select How would I add the combo box and the code to the new sheet thanks "Tom Ogilvy" wrote: On the sheet, do edit=Goto=special and select Last Cell. Do you end up where you expected or are you way past where your data ends. If you ended up well past where your data ends, then see Debra Dalgleish's site for some hints: http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tom Ogilvy "Nigel" wrote in message ... I did some investigation and it is a new worksheet I added that is causing the size increase, strangely tho the worksheets has 1 list box and 1 column of data which is only 55 names I Exported the offending sheet to a new workbook and deleted everything from it, all the code everything this one sheet is increasing the size of the workbook by over 3.6 MB Anyone got any ideas Thanks "Vacation's Over" wrote: lines of code are small what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
It isn't clear what you are doing. Do you want to set up a new workbook
using code - this activity will be part of your application. Or are you just creating a new workbook manually and want to know how to copy the combobox and the code associated with the combobox. You can copy a combobox by selecting it and doing edit=copy, then select the new sheet and do edit=Paste for code, you can go to the code, highlight it, and do Ctrl+C. Go to the new module/location and click in the module, then do Ctrl+V If it is more complex than that, then provide some details. For instance, the Control Toolbox Toolbar has a combobox, as does the Forms toolbar and you can get a dropdown in a cell with the list option of Data validation. The first two can have code associated with them, but in each case, the location of the code would be different. So the more information you provide, the better people can provide assistance. -- Regards, Tom Ogilvy "Nigel" wrote in message ... OK I think I have the issue, I am moving\Copying the sheet to a new workbook, If I just copy the data and paste into the new sheet it is much smaller, My dilema now is I need to have the combo box created on the new shet and the code added behind the scene The rows for the combo box are in A1 to A 50 and the code behind the combon box is NewSheet = Range("B1") Sheets(NewSheet).Select How would I add the combo box and the code to the new sheet thanks "Tom Ogilvy" wrote: On the sheet, do edit=Goto=special and select Last Cell. Do you end up where you expected or are you way past where your data ends. If you ended up well past where your data ends, then see Debra Dalgleish's site for some hints: http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tom Ogilvy "Nigel" wrote in message ... I did some investigation and it is a new worksheet I added that is causing the size increase, strangely tho the worksheets has 1 list box and 1 column of data which is only 55 names I Exported the offending sheet to a new workbook and deleted everything from it, all the code everything this one sheet is increasing the size of the workbook by over 3.6 MB Anyone got any ideas Thanks "Vacation's Over" wrote: lines of code are small what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
Ok I am using code to copy the data from one sheet into another sheet on a
new workbook, I was looking for some code that that I could add to my current code that would automatically create a combo box on the new worksheet based on the following information The rows for the combo box are in A1 to A 50 and the code behind the combo box is NewSheet = Range("B1") Sheets(NewSheet).Select "Tom Ogilvy" wrote: It isn't clear what you are doing. Do you want to set up a new workbook using code - this activity will be part of your application. Or are you just creating a new workbook manually and want to know how to copy the combobox and the code associated with the combobox. You can copy a combobox by selecting it and doing edit=copy, then select the new sheet and do edit=Paste for code, you can go to the code, highlight it, and do Ctrl+C. Go to the new module/location and click in the module, then do Ctrl+V If it is more complex than that, then provide some details. For instance, the Control Toolbox Toolbar has a combobox, as does the Forms toolbar and you can get a dropdown in a cell with the list option of Data validation. The first two can have code associated with them, but in each case, the location of the code would be different. So the more information you provide, the better people can provide assistance. -- Regards, Tom Ogilvy "Nigel" wrote in message ... OK I think I have the issue, I am moving\Copying the sheet to a new workbook, If I just copy the data and paste into the new sheet it is much smaller, My dilema now is I need to have the combo box created on the new shet and the code added behind the scene The rows for the combo box are in A1 to A 50 and the code behind the combon box is NewSheet = Range("B1") Sheets(NewSheet).Select How would I add the combo box and the code to the new sheet thanks "Tom Ogilvy" wrote: On the sheet, do edit=Goto=special and select Last Cell. Do you end up where you expected or are you way past where your data ends. If you ended up well past where your data ends, then see Debra Dalgleish's site for some hints: http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tom Ogilvy "Nigel" wrote in message ... I did some investigation and it is a new worksheet I added that is causing the size increase, strangely tho the worksheets has 1 list box and 1 column of data which is only 55 names I Exported the offending sheet to a new workbook and deleted everything from it, all the code everything this one sheet is increasing the size of the workbook by over 3.6 MB Anyone got any ideas Thanks "Vacation's Over" wrote: lines of code are small what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Increasing Size of Worksheet
If you want to write code with code,
See Chip Pearson's site on programming in the VBE http://www.cpearson.com/excel/vbe.htm to add a combobox from the control toolbox toolbar Sub ABC() Dim cbox As MSForms.ComboBox Dim OleObj As OLEObject With ActiveSheet.Range("B9") Set OleObj = ActiveSheet.OLEObjects.Add( _ ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=.Left, _ Top:=.Top, _ Width:=.Width, _ Height:=.Height) OleObj.ListFillRange = "'" & ActiveSheet.Name _ & "'!A1:A20" Set cbox = OleObj.Object End With End Sub -- Regards, Tom Ogilvy "Nigel" wrote in message ... Ok I am using code to copy the data from one sheet into another sheet on a new workbook, I was looking for some code that that I could add to my current code that would automatically create a combo box on the new worksheet based on the following information The rows for the combo box are in A1 to A 50 and the code behind the combo box is NewSheet = Range("B1") Sheets(NewSheet).Select "Tom Ogilvy" wrote: It isn't clear what you are doing. Do you want to set up a new workbook using code - this activity will be part of your application. Or are you just creating a new workbook manually and want to know how to copy the combobox and the code associated with the combobox. You can copy a combobox by selecting it and doing edit=copy, then select the new sheet and do edit=Paste for code, you can go to the code, highlight it, and do Ctrl+C. Go to the new module/location and click in the module, then do Ctrl+V If it is more complex than that, then provide some details. For instance, the Control Toolbox Toolbar has a combobox, as does the Forms toolbar and you can get a dropdown in a cell with the list option of Data validation. The first two can have code associated with them, but in each case, the location of the code would be different. So the more information you provide, the better people can provide assistance. -- Regards, Tom Ogilvy "Nigel" wrote in message ... OK I think I have the issue, I am moving\Copying the sheet to a new workbook, If I just copy the data and paste into the new sheet it is much smaller, My dilema now is I need to have the combo box created on the new shet and the code added behind the scene The rows for the combo box are in A1 to A 50 and the code behind the combon box is NewSheet = Range("B1") Sheets(NewSheet).Select How would I add the combo box and the code to the new sheet thanks "Tom Ogilvy" wrote: On the sheet, do edit=Goto=special and select Last Cell. Do you end up where you expected or are you way past where your data ends. If you ended up well past where your data ends, then see Debra Dalgleish's site for some hints: http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tom Ogilvy "Nigel" wrote in message ... I did some investigation and it is a new worksheet I added that is causing the size increase, strangely tho the worksheets has 1 list box and 1 column of data which is only 55 names I Exported the offending sheet to a new workbook and deleted everything from it, all the code everything this one sheet is increasing the size of the workbook by over 3.6 MB Anyone got any ideas Thanks "Vacation's Over" wrote: lines of code are small what may happen is that in developing those final lines some garbage may collect. try a code cleaner http://www.appspro.com/Utilities/CodeCleaner.htm "Nigel" wrote: I added some code to a worksheet, a macro and a list box and it increased the size of the worksheet quite substantially, is there anyway to prevent just a few lines of code increasing spreadsheet size |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increasing file size | Excel Discussion (Misc queries) | |||
Size of my comments keeps increasing | Excel Discussion (Misc queries) | |||
increasing spreadsheet size | Excel Discussion (Misc queries) | |||
Increasing Chart Size | Charts and Charting in Excel | |||
Increasing file size... | Excel Programming |