Can Turn Off Internal "Undo" Stack in VBA?

[Larry Adams]

I have an apllication where I examine 5500 rows to see if empty and, if so,
"Hide" the row. Otherwise, "Unhide". After this macro has been run a few
times, the macro slows to a crawl.

My hypothesis is that Excel's internal "Undo" feature is tracking these
changes and I am filling up memory set aside for "Undo"s -- which, then
causes Excel to clear the oldest entry in the stack in order to add the
next. At which point it crawls.

I've seen this outside of VBA in Exel proper when I go to do a "Find and
Replace All" on a large data retrieval area. The replace zips along fine,
then slows, then crawls.

I'm wondering if my VBA "Unhide"/"Hide" code is running into something
similar. Hope I've described the symptoms well enough. Any ideas would be
welcome. Thanks.

 

[JE McGimpsey]

I'm not sure what causes the slowdown in most cases, but I suspect that,
since macros clear XL's Undo stack, it isn't the culprit.

 

[Tim Williams]

Does your macro use Find() ?

In my experience it may not be as fast as you expect: sometimes there are
other faster approaches.
Maybe you could show your code?

Tim

 

[Larry Adams]

No, the find was an external example of the same type of slowdown I am
getting here. All I am doing here is to examine a cell on each of the 5500
rows and, if "1", unhide the row, a "0", hide. Below is the code.

The slow down occurs within the loop. If I <CTRL><BREAK> and debug, I can
check the i loop value. Normally slows arounf 800 or so. If I continue the
macro and interrupt again, the i counter is increasing, so I know I am not
totally locked. Just slow. Thanks.

Sub A20_DeptShowNonZero() '### Starts with Active Cell ###
Dim i, j, curr_lvl, next_lvl As Integer
Dim c, row As String
application.ScreenUpdating = False
Worksheets("DEPT").Activate
c = ActiveCell.Address
row = ActiveCell.row
With Worksheets("DEPT").range("D" & row)
curr_lvl = .Offset(0, -3)
For i = 1 To 20000
If .Offset(i, 0) = "" Then
Exit For
End If
next_lvl = .Offset(i, -3)
If next_lvl <= curr_lvl Then
Exit For
Else
Rows("" & (row + i) & ":" & (row + i) & "").Select
If .Offset(i, 13) = 1 Then
selection.EntireRow.Hidden = False
Else
selection.EntireRow.Hidden = True
End If
End If
Next i
End With
Worksheets("DEPT").range(c).Select
End Sub

 

[Larry Adams]

Per a Google search, I saw where Excel does clear the stack on the first
change to a sheet. I'm wondering, however, if that may be on the first
change to a cell -- and maybe not hide/unhide rows.

So I'm rewriting the code to hide/unhide contiguous rows together in one
statement, rather than one at a time. Will pass out to people at work today
to try and see how it goes. But it is stop gap only.

So the other thought is to throw in an innoculous cell change.

But if this has nothing to do with the undo stack, I still have a problem.
I've included code now under the other reply. Thanks again.

 

[Susan]

just a not-a-guru idea...........
since you suspect that the stack gets cleared on the first change to a
cell, why don't you try adding some sort of a change to a cell @ the
bottom of your macro?

such as (i know syntax may not be correct):

worksheet.range("xx2") = "327"
worksheet.range("xx2").clearcontents

maybe that would clear the stack so then it can be run again as the
first time?
just an idea.

susan

Per a Google search, I saw where Excel does clear the stack on the first
change to a sheet. I'm wondering, however, if that may be on the first
change to a cell -- and maybe not hide/unhide rows.

So I'm rewriting the code to hide/unhide contiguous rows together in one
statement, rather than one at a time. Will pass out to people at work today
to try and see how it goes. But it is stop gap only.

So the other thought is to throw in an innoculous cell change.

But if this has nothing to do with the undo stack, I still have a problem.
I've included code now under the other reply. Thanks again.

 

[Susan]

So the other thought is to throw in an innoculous cell change

duh. see, i thought it was a good idea!

that'll teach me to read more carefully before i throw out an idea.
susan

 

[Dave Peterson]

(Saved from a previous post)

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.

 

[Larry Adams]

Susan, still appreciate the thought. The code suggested by the site I found
is fairly generic -- and seems to be helping. Thanks.

Sub ClearUndo()
Range("A1").copy Range("A1")
End Sub

 

[Larry Adams]

Dave, I think you've identified a factor with this as well. As the page is
formatted to print to fit -- across all 5500 rows, with the assumption that
there will normally be no more that 50 or so left unhidden. But I start
with 5500 rows unhidden, and pull out one line at a time.

I will look into. Thanks!!