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

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)


--
royUK

Hope that helps, RoyUK
For tips & examples visit 'my web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35550

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

"royUK" wrote:


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)


--
royUK

Hope that helps, RoyUK
For tips & examples visit 'my web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35550

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

"Risky Dave" wrote in message
...
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

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

"Risky Dave" wrote:

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

DataHog;128277 Wrote:
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

I'd just thought of that one. This code might help re-setting the last
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 Sub
--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35550

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


"royUK" wrote in message
...

DataHog;128277 Wrote:
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

I'd just thought of that one. This code might help re-setting the last
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 Sub
--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35550