Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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. In article , "Larry Adams" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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 "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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. "JE McGimpsey" wrote in message ... 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. In article , "Larry Adams" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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 On Apr 19, 7:22 am, "Larry Adams" wrote: 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. "JE McGimpsey" wrote in message ... 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. In article , "Larry Adams" wrote: 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.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
On Apr 19, 7:22 am, "Larry Adams" wrote:
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
(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 wrote: 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. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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 "Susan" wrote in message ups.com... On Apr 19, 7:22 am, "Larry Adams" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Turn Off Internal "Undo" Stack in VBA?
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!! "Dave Peterson" wrote in message ... (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 wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn off "CALCULATE" on bottom of Excel worksheet. near "Ready" | Excel Discussion (Misc queries) | |||
"internal margin" and axis offsets (excel 2007) | Charts and Charting in Excel | |||
"Out of Stack Space" Macro Error | Excel Discussion (Misc queries) | |||
Run time error - "out of stack space" | Excel Programming | |||
Run Time Error "28" - Out of stack space? | Excel Programming |