Code Increasing Size of Worksheet

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

No nothing special there, If I export to a new workbook it is 3.6MB, just wierd

 

[Guest]

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

 

[Guest]

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]

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.

 

[Guest]

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]

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