Unexplained size increase

[Risky Dave]

Hi,

I've beend eveloping a quite complex spreadsheet that will be used as a
database and analysis tool (I realise it' snot the best application for this
- especially the former- but it's what I have to use). It has multiple pages,
several hundred lines of code and has suddenly jumped from about 400K to over
7M in size.

Can anyone suggest what might have caused this sudden increase in size?

There is currently no data populating the tool, so this appears to be all
pages and code, which doesn't seem right to me.

TIA

Dave

 

[royUK]

This often happens when working with developing code. A good tool that
may help is Rob Bovey's 'Code Cleaner'
(http://www.appspro.com/Utilities/CodeCleaner.htm)

 

[Risky Dave]

RoyUk,

Thanks for the quick reply.

I have now run Rob's application and it doesn't seem to have made much
difference. Can anyone else provide any suggfestions?

TIA

Dave

 

[Peter T]

It is normal for a file with uncompiled code to expand 2-3 times once
compiled or run a few times (excl rest of sheet stuff). But that wouldn't
explain your massive increase.

Best guess is formats have been applied to large areas of unused cells.
Following copies each sheet to a new workbook, saves and closes it, reads
its file size and deletes the file. Look for one or more sheets that appear
to big.

Regards,
Peter T

 

[DataHog]

On your worksheets press Control+End (key) - this will take you the last used
cell. You will probably will find that you have 1000s of rows of blank data.
Simply highlight the blank rows and delete the rows. Then save the workbook
and check the file size.

Jim K

 

[royUK]

On your worksheets press Control+End (key) - this will take you the las
used
cell. You will probably will find that you have 1000s of rows of blan
data.
Simply highlight the blank rows and delete the rows. Then save th
workbook
and check the file size.

Jim K

I'd just thought of that one. This code might help re-setting the las
cell

Code
-------------------

Option Explicit

Sub xlFileReducer()
Dim ws As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Application.ScreenUpdating = False

On Error Resume Next

For Each ws In ThisWorkbook.Worksheets
With ws
LastRow = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
.Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).Delete
.Range(.Cells(LastRow + 1, 1), .Cells(.Rows.Count, .Columns.Count)).Delete
End With
Next ws

On Error GoTo 0

Application.ScreenUpdating = True

End Su

 

[Peter T]

Couple of small things -

I'd change LookIn:=xlValues to xlFormulas (in case a formula returns a "")
and
add a .UsedRange to reset the UR to avoid misleading future use of ctrl-end
(at least normally it will reset)

Regards,
Peter T