Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

That should help quite a bit.

The Application.ScreenUpdating = False prevents Excel from showing you all
of the changes happening in "real time" - keeps the screen from flickering as
you go to different sheets or cells and can speed things up by a factor of 30
or more. But since you're using ActiveSheet.Range(...) instead of first
selecting the cells and then setting their value to "", it's probably not
going to be a huge time saver (you're already doing it very efficiently) but
will still help some.

Setting the calculation to Manual while doing all of these updates may be
the big one for you - as you're changing the various values on the sheet(s),
Excel is trying to recalculate all dependent values based on the new value
you're setting. Depending on how many cells are downstream-dependent on the
ones you're clearing, this could be a big improvement. None of the interim
calculations will take place, just one final one at the end of the process
when you turn automatic calculation back on.

"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Thanks...I'll test it out shortly.

One thing I noticed is that even updating a cell manually (typing over a
value or even changing the background color) takes way longer than it should.

Excel seems to be holding something in memory somewhere...my current
MemUsage in my Task Manager is almost 100MB!

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"