workbook macros now run VERY slowly ... ideas please

[wdeleo]

I made some revisions to a workbook and when I went to run the macros later I
noticed that everything was running terribly slow (seconds to minutes for
something that was instantaneous before). The workbook size had also
increased from 1.5 megs to 40 megs. I identified the reason for the size
increase (autofilter copy/paste included too many rows) and reduced the size
back to the 1.5 megs. BUT, it still runs at snail's pace. The CPU stays
pegged at about 50% while the macros are running, but it shouldn't be taking
so long.

Does anyone have a suggestion as to how I might be able to identify/rectify
the problem?

TIA

WD

 

[Josh Sale]

Visible page breaks can do this. Try adding this line of code:
activeSheet.DisplayPageBreaks = False
at the start of the code and after any macro code that will cause page
breaks to be added to the worksheet.

I'm guessing that you're really running the CPU at 100% but that you're on a
hyperthreaded or dual core system and so it only shows as 50%.

josh

 

[wdeleo]

I tried what you suggested but it did not help. Other suggestions? It is
VERY slow.

Thanks

 

[wdeleo]

If I turn calculation to manual, one particular simple macro takes much less
than a second. If I manually calculate (F9), calculation takes ~0.5 seconds.
If I turn calc back to manual, the same macro takes about one MINUTE. How
can I figure out where the hold-up is???

 

[gonecrazybacksoon]

Is it possible for u to post the code here, so I can see what it is
doing?

 

[wdeleo]

No problem ... thanks so much for asking. I'm stumped. Everything seems to
run slow, but here is the example I described above:

******************************************

Sub new_adhesive_start()

'call clear form sub *** SEE BELOW***
Clear_new_adhesive_form

Worksheets("Define New Adhesive").Select
Worksheets("Define New Adhesive").Range("M8").Select

End Sub

Sub Clear_new_adhesive_form()

Dim n As Integer
Dim ddm_name As String

'clear all fields
Worksheets("Define New Adhesive").Range("M8").Value = ""
Worksheets("Define New Adhesive").Range("M14").Value = ""
For n = 1 To 15
Worksheets("Variables").Cells(n + 23, 4).Value = 0
Worksheets("Define New Adhesive").Cells((n + 1) * 2, 9).Value = ""
Next

'enable top ddm
Sheets("Define New Adhesive").DropDowns("Drop Down 1").Enabled = True

'disable all but top ddm
For n = 2 To 15
ddm_name = "Drop Down " & n
Sheets("Define New Adhesive").DropDowns(ddm_name).Enabled = False
Next

End Sub

*********************************************

Thanks again,
WD

 

[gonecrazybacksoon]

OK, now you have me stumped as well. I can't find anything obvious in
the code that should be slowing it down. Initially, you said the
problem started after some revisions that u made. Could you please
elaborate on that. Also, what is dropdown 1 doing. Is it a combobox? If
the entire file is not too big, and not too sensitive info, could you
please email it to me... (e-mail address removed).

 

[wdeleo]

file sent Tues AM.

Thanks

OK, now you have me stumped as well. I can't find anything obvious in
the code that should be slowing it down. Initially, you said the
problem started after some revisions that u made. Could you please
elaborate on that. Also, what is dropdown 1 doing. Is it a combobox? If
the entire file is not too big, and not too sensitive info, could you
please email it to me... (e-mail address removed).