spreadsheet file size is huge! Don't know why 1

[soLjd]

I wrote some vba code for a co-worker yesterday. The Excel workbook consists of 8 sheets, minimally formatted with two labels and a vba textbox at the top. There are 100 rows and six columns that are visible (not hidden). There are only three columns with formulas in them and all return a Nullstring unless there is data in one of three columns. As for the VBA code, I inserted a module and wrote a sub to populate the vba textbox at the top of each sheet and, upon calculation of the active sheet, make a call to that sub in the midule. With very little data in the workbook, the file size is close to 34 Mb. Can anyone help me shrink this thing down?

Here is the sub I wrote:

Sub FillText()
Dim i As Integer
Dim x As String

i = 1
' column m = indicator of last row (amount)
ActiveSheet.Range("m1").Select

' If no data on sheet, populate txtbox w/ starting balance
If ActiveCell.Value = "1" Then
If ActiveSheet.Name <> "SCMR" Then
ActiveSheet.txtBal.Text = "$" & Cells(1, 11).Value
Else
ActiveSheet.TextBox1.Text = "$" & Cells(1, 11).Value
End If
Else

' find row w/ last amount
Do Until ActiveCell.Value = "1"
Cells(i, 13).Select
x = Cells(i, 11).Value
i = i + 1
Loop

' populate txtbox w/ last amount
If ActiveSheet.Name <> "SCMR" Then
ActiveSheet.txtBal.Text = "$" & x
Else
Worksheets(6).TextBox1.Text = "$" & x
End If

'go to 1st cell of next row
Cells(i, 1).Select
End If
End Sub

 

[Gavona]

Before you run the code, what size is the workbook? I suspect that it is bloated to start with. I 'tested' you code on a blank workbook and it does not become bloated.

Try moving to each sheet in term and finding what excel regards as the last cell by pressing Ctrl-End. Any suprises?

Often imported data causes problems with this. Easily solved with code or manually. Plenty of threads on this forum. Some of the FAQs about last cell may help too (delete everyrow below the real last cell then save the workbook and check the change in size)


Gavin

 

[soLjd]

Thanks Gavin!

For some reason my first sheet in the workbook was +33 Mb without any data. Couldn't figure out what was hidden in the sheet, but I deleted it, created a new sheet with formats\formulas\objects copied from another sheet. Now the whole workbook is only 206 Kb even with all the data the user entered.
Just out of curiousity, anyone have any guesses why/how the 1st sheet got so bloated? There wasn't any extraneous code or formatting and, like I said in the original post, there were only six columns and 100 rows visible with no formatting in the hidden cells. Even more perplexing, I used the first sheet as a template for the rest of the sheets in the workbook - just copied and pasted all from the 1st to the others. It seems to me that if there was some stuff clogging up sheet1 that it would transfer over to the rest of the sheets.

Thanks again,

soLjd

 

[Gavona]

anyone have any guesses why/how the 1st sheet got so bloated?

You do not give much to go on.

Was the last cell on that troublesome worksheet unexpected?
faq707-2115 says

As has been stated many times in this and other forums, excel gets "confused" as to how many cells actually have data in. This happens especially when data is entered and then cleared as opposed to deleting the cell. In this instance, the UsedRange property of the worksheet becomes incorrect. Here are 3 ways to get round it

faq68-619 shows how to manually address that problem.

Bloating can also be caused by pivot tables, links, graphic objects, corruption. I have come across named ranges which referred to other workbooks and deleting them seemed to reduce file size.


Gavin

 

[soLjd]

The last cell was actually exactly what I expected [cells(100,11)]. There were no links, graphics, pivot tables (I know they're useful but I hate those damn things!) or named ranges referring to other workbooks. There really wasn't much of anything - this was a really simple spreadsheet. As I said before, there's only a textbox, a couple labels, a very small amount of formatting at the top of the sheets and three columns with actual formulas. I guess I'll just chalk it up to corruption.

I appreciate your time and insight, Gavin.

Thanks again for your help,

soLjd