any ideas why execution speed varies?

[c1802362]

Hello, everyone. Need a sanity check.

My wife uses an Excel reporting template from her work that was
provided by her corporate office. The template captures rows of data.
Where two or more rows of contiguous data are in a similar group, the
decision was made to merge cells in column E across these rows.

The creator of the spreadsheet added two buttons at the top of the
page. The desired outcome of clicking one button is that it hides any
row (or rows) where the content of the corresponding cells in column E
have an ‘O’ in them. The second button’s purpose is to restore the
spreadsheet to its original view of all rows visible (and column E
merged appropriately). The creator of the spreadsheet evidently turned
on the macro recorder and figured all was well. When I got the
template, neither button worked as advertised.

So, I’ve written the code below to manipulate the template as desired.
My code unmerges each group of cells in Column E with an ‘O’ value,
adds a sequential numerical ID, then hides the row. The code skips any
section head that has gray shading or where cells in column E are
merged, but don’t have the ‘O’ value.

When the original view needs to be restored, column E of the hidden
rows are remerged using the sequential ID, and replaced with the ‘O’.
I’m using an open cell in the template header (E4) to flag which state
the template is in (‘O’ for original, ‘X’ for unmerged)

My problem: running this on my wife’s company network, it’ll either
run instantaneously, with barely a perceptible delay in execution, or
it takes 28 seconds to execute the hiding routine (HideCells) and 1-2
seconds to restore the template to its original form (ViewCells).
There’s no rhyme or reason as to how fast it decides to run.

Is there something in my code that causes the code to arbitrarily run
slow or fast? Or is there something in my wife’s corporate network
that is the problem?

Her network is running Windows 2003, 2007, and 2010 (large corporation
with staggered software upgrades). I haven’t seen a difference between
the software versions when she’s had others in her office use the
template.

Art

Here’s the code:


Option Explicit

Dim i As Integer, intRowCount As Integer

Const TargetRow = 7 ' starting row of data
Const TargetCol = 5 ' column of interest
Const LetterO = "O"
Const LetterX = "X"



Private Sub HideCells()

'***********************************************************************
' This routine hides all rows of data that have an 'O' in column E
' including those cells in column E merged together. Algorithm skips
' over any rows with gray shading or those where column E is empty.
' Algorithm then unmerges cells in column E and assigns a sequential
' integer ID to those cells previously merged
'***********************************************************************

Dim rngActive As Range, intMergeCounter As Integer

If Cells(4, TargetCol) = LetterX Then Exit Sub 'checks flag

Application.ScreenUpdating = False

' index to identify which rows stay together
' replaces 'O' in column E while hidden
intMergeCounter = 1

intRowCount = Range("C65000").End(xlUp).Row

For i = intRowCount To TargetRow Step -1

' skip any row with gray shaded headings
If Cells(i, TargetCol).Interior.ColorIndex <> 15 Then

' skip any rows where column E is empty
If Cells(i, TargetCol) = LetterO Then
Cells(i, TargetCol).Activate

' capture merged areas in column E and add unique
' integer to all cells in captured range
Set rngActive = ActiveCell.MergeArea
With rngActive
.UnMerge
.Value = intMergeCounter
End With

' once unique integer is added, hide rows
rngActive.EntireRow.Hidden = True
intMergeCounter = intMergeCounter + 1
End If
End If
Next i

Cells(4, TargetCol) = LetterX ' sets flag

Application.ScreenUpdating = True

End Sub



Private Sub ViewCells()

'***********************************************************************
' This routine unhides all rows of data, merges the cells in column E
' by integer ID, then replaces the ID in each merged cell with an 'O'
'***********************************************************************

Dim intMaxCount As Integer, x As Integer, intAdder As Integer

If Cells(4, TargetCol) = LetterO Then Exit Sub ' checks flag

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

intRowCount = Range("C65000").End(xlUp).Row

' unhide all rows of data
Rows(TargetRow & ":" & intRowCount).EntireRow.Hidden = False

' count the number of sequential integer IDs generated
intMaxCount =
Application.WorksheetFunction.Max(Columns(TargetCol))

x = 1 ' sequential integer ID counter

Do Until x > intMaxCount

intAdder = 1 ' merged cell counter

With Columns(TargetCol)
.Find(What:=x, After:=Cells(1, TargetCol), LookIn:=xlFormulas,
lookat:=xlWhole).Activate
Do Until ActiveCell.Offset(intAdder, 0) <> ActiveCell
intAdder = intAdder + 1 ' counts the number of rows that
need to be merged together
Loop

intAdder = intAdder - 1 'corrects count
End With

With Range(ActiveCell, ActiveCell.Offset(intAdder, 0))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.MergeCells = True
.FormulaR1C1 = LetterO
End With

x = x + 1
Loop

Cells(4, TargetCol) = LetterO ' sets flag

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

 

[joeu2004]

Is there something in my code that causes the code
to arbitrarily run slow or fast?

Is almost impossible for anyone to answer that question at arm's length.
There are so many possible variables.

But generally, it is prudent to bracket the bulk of the code in the macro
with the following:

Dim oldcalc
With Application
oldcalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
[... the rest of your macro ...]
With Application
.EnableEvents = True
.Calculation = oldcalc
.ScreenUpdating = True
End With

Order is important in second (last) With Application block. You did
ScreenUpdating, but not the other two.

Caveat: oldcalc is xlCalculationAutomatic (usually it is),
..Calculation=oldcalc will trigger a recalculation cycle, even if none were
needed :-(. At least, that is true in XL2003.

Or is there something in my wife’s corporate network
that is the problem?
Her network is running Windows 2003, 2007, and 2010
(large corporation with staggered software upgrades).

Note that "networks" do not run software; computers do. What might be
significant is:


1. Is your wife running Excel on her own computer, or is she running Excel
on a remote computer, e.g. using MS Remote Desktop or a third-party's
Citrix?

If so, the intermittent delays that you describe could reflect the load
on the remote computer.


2. You say: "The template captures rows of data". By "capture", do you
mean that it is accessing data over the network, either by hyperlinks in
Excel or by a macro that pulls the data into the worksheet?

If so, failing to disable auto calculation and event macros could
contribute to the intermittent delays that you describe.

 

[Charlotte E]

My guess is pagebreaks!

If pagebreaks are displayed while running the macro it will take 'ages' if
connected to a network printer!

So, when the macro runs fast, pagebreaks are not displayed, and the other
way around!

Put this at the begining of your code:

ActiveSheet.DisplayAutomaticPagebreaks = False
ActiveSheet.DisplayPagebreaks = False

Don't know the difference of the two above lines, so I always just use both



CE



Hello, everyone. Need a sanity check.

My wife uses an Excel reporting template from her work that was
provided by her corporate office. The template captures rows of data.
Where two or more rows of contiguous data are in a similar group, the
decision was made to merge cells in column E across these rows.

The creator of the spreadsheet added two buttons at the top of the
page. The desired outcome of clicking one button is that it hides any
row (or rows) where the content of the corresponding cells in column E
have an ‘O’ in them. The second button’s purpose is to restore the
spreadsheet to its original view of all rows visible (and column E
merged appropriately). The creator of the spreadsheet evidently turned
on the macro recorder and figured all was well. When I got the
template, neither button worked as advertised.

So, I’ve written the code below to manipulate the template as desired.
My code unmerges each group of cells in Column E with an ‘O’ value,
adds a sequential numerical ID, then hides the row. The code skips any
section head that has gray shading or where cells in column E are
merged, but don’t have the ‘O’ value.

When the original view needs to be restored, column E of the hidden
rows are remerged using the sequential ID, and replaced with the ‘O’.
I’m using an open cell in the template header (E4) to flag which state
the template is in (‘O’ for original, ‘X’ for unmerged)

My problem: running this on my wife’s company network, it’ll either
run instantaneously, with barely a perceptible delay in execution, or
it takes 28 seconds to execute the hiding routine (HideCells) and 1-2
seconds to restore the template to its original form (ViewCells).
There’s no rhyme or reason as to how fast it decides to run.

Is there something in my code that causes the code to arbitrarily run
slow or fast? Or is there something in my wife’s corporate network
that is the problem?

Her network is running Windows 2003, 2007, and 2010 (large corporation
with staggered software upgrades). I haven’t seen a difference between
the software versions when she’s had others in her office use the
template.

Art

Here’s the code:


Option Explicit

Dim i As Integer, intRowCount As Integer

Const TargetRow = 7 ' starting row of data
Const TargetCol = 5 ' column of interest
Const LetterO = "O"
Const LetterX = "X"



Private Sub HideCells()

'***********************************************************************
' This routine hides all rows of data that have an 'O' in column E
' including those cells in column E merged together. Algorithm skips
' over any rows with gray shading or those where column E is empty.
' Algorithm then unmerges cells in column E and assigns a sequential
' integer ID to those cells previously merged
'***********************************************************************

Dim rngActive As Range, intMergeCounter As Integer

If Cells(4, TargetCol) = LetterX Then Exit Sub 'checks flag

Application.ScreenUpdating = False

' index to identify which rows stay together
' replaces 'O' in column E while hidden
intMergeCounter = 1

intRowCount = Range("C65000").End(xlUp).Row

For i = intRowCount To TargetRow Step -1

' skip any row with gray shaded headings
If Cells(i, TargetCol).Interior.ColorIndex <> 15 Then

' skip any rows where column E is empty
If Cells(i, TargetCol) = LetterO Then
Cells(i, TargetCol).Activate

' capture merged areas in column E and add unique
' integer to all cells in captured range
Set rngActive = ActiveCell.MergeArea
With rngActive
.UnMerge
.Value = intMergeCounter
End With

' once unique integer is added, hide rows
rngActive.EntireRow.Hidden = True
intMergeCounter = intMergeCounter + 1
End If
End If
Next i

Cells(4, TargetCol) = LetterX ' sets flag

Application.ScreenUpdating = True

End Sub



Private Sub ViewCells()

'***********************************************************************
' This routine unhides all rows of data, merges the cells in column E
' by integer ID, then replaces the ID in each merged cell with an 'O'
'***********************************************************************

Dim intMaxCount As Integer, x As Integer, intAdder As Integer

If Cells(4, TargetCol) = LetterO Then Exit Sub ' checks flag

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

intRowCount = Range("C65000").End(xlUp).Row

' unhide all rows of data
Rows(TargetRow & ":" & intRowCount).EntireRow.Hidden = False

' count the number of sequential integer IDs generated
intMaxCount =
Application.WorksheetFunction.Max(Columns(TargetCol))

x = 1 ' sequential integer ID counter

Do Until x > intMaxCount

intAdder = 1 ' merged cell counter

With Columns(TargetCol)
.Find(What:=x, After:=Cells(1, TargetCol), LookIn:=xlFormulas,
lookat:=xlWhole).Activate
Do Until ActiveCell.Offset(intAdder, 0) <> ActiveCell
intAdder = intAdder + 1 ' counts the number of rows that
need to be merged together
Loop

intAdder = intAdder - 1 'corrects count
End With

With Range(ActiveCell, ActiveCell.Offset(intAdder, 0))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.MergeCells = True
.FormulaR1C1 = LetterO
End With

x = x + 1
Loop

Cells(4, TargetCol) = LetterO ' sets flag

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

 

[c1802362]

Is there something in my code that causes the code
to arbitrarily run slow or fast?

Is almost impossible for anyone to answer that question at arm's length.
There are so many possible variables.

But generally, it is prudent to bracket the bulk of the code in the macro
with the following:

Dim oldcalc
With Application
   oldcalc = .Calculation
   .ScreenUpdating = False
   .Calculation = xlCalculationManual
   .EnableEvents = False
End With
[... the rest of your macro ...]
With Application
   .EnableEvents = True
   .Calculation = oldcalc
   .ScreenUpdating = True
End With

Order is important in second (last) With Application block.  You did
ScreenUpdating, but not the other two.

Caveat:  oldcalc is xlCalculationAutomatic (usually it is),
.Calculation=oldcalc will trigger a recalculation cycle, even if none were
needed :-(.  At least, that is true in XL2003.

Or is there something in my wife s corporate network
that is the problem?
Her network is running Windows 2003, 2007, and 2010
(large corporation with staggered software upgrades).

Note that "networks" do not run software; computers do.  What might be
significant is:

1. Is your wife running Excel on her own computer, or is she running Excel
on a remote computer, e.g. using MS Remote Desktop or a third-party's
Citrix?

   If so, the intermittent delays that you describe could reflect theload
on the remote computer.

2. You say:  "The template captures rows of data".  By "capture", do you
mean that it is accessing data over the network, either by hyperlinks in
Excel or by a macro that pulls the data into the worksheet?

   If so, failing to disable auto calculation and event macros could
contribute to the intermittent delays that you describe.

Thanks for the suggestions - I'll try them

1) My wife's computer has MS Office loaded on it and offline the
software runs fine. When connected to the network, it runs slower if
there's a bunch of network traffic (almost as if it needs to be
communicating with the network)

2) when I said data, I mean the spreadsheet she downloads resides on a
remote server accessible by numerous other groups that add their data
to it. She then downloads and reduces the data

Art

 

[c1802362]

My guess is pagebreaks!

If pagebreaks are displayed while running the macro it will take 'ages' if
connected to a network printer!

So, when the macro runs fast, pagebreaks are not displayed, and the other
way around!

Put this at the begining of your code:

    ActiveSheet.DisplayAutomaticPagebreaks = False
    ActiveSheet.DisplayPagebreaks = False

Don't know the difference of the two above lines, so I always just use both

Another good idea I'll try - thanks

Art

 

[Martin Brown]

Is there something in my code that causes the code
to arbitrarily run slow or fast?

Is almost impossible for anyone to answer that question at arm's length.
There are so many possible variables.

But generally, it is prudent to bracket the bulk of the code in the macro
with the following:

Dim oldcalc
With Application
oldcalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
[... the rest of your macro ...]
With Application
.EnableEvents = True
.Calculation = oldcalc
.ScreenUpdating = True
End With

Order is important in second (last) With Application block. You did
ScreenUpdating, but not the other two.

Caveat: oldcalc is xlCalculationAutomatic (usually it is),
.Calculation=oldcalc will trigger a recalculation cycle, even if none were
needed :-(. At least, that is true in XL2003.

Or is there something in my wife s corporate network
that is the problem?
Her network is running Windows 2003, 2007, and 2010
(large corporation with staggered software upgrades).

Note that "networks" do not run software; computers do. What might be
significant is:

1. Is your wife running Excel on her own computer, or is she running Excel
on a remote computer, e.g. using MS Remote Desktop or a third-party's
Citrix?

If so, the intermittent delays that you describe could reflect the load
on the remote computer.

2. You say: "The template captures rows of data". By "capture", do you
mean that it is accessing data over the network, either by hyperlinks in
Excel or by a macro that pulls the data into the worksheet?

If so, failing to disable auto calculation and event macros could
contribute to the intermittent delays that you describe.

Thanks for the suggestions - I'll try them

1) My wife's computer has MS Office loaded on it and offline the
software runs fine. When connected to the network, it runs slower if
there's a bunch of network traffic (almost as if it needs to be
communicating with the network)

It could well be something to do with the way that Microsoft Office
generates absolute paths in spreadsheet links - so that when attached to
the network it is continually referencing the original linked file(s).

2) when I said data, I mean the spreadsheet she downloads resides on a
remote server accessible by numerous other groups that add their data
to it. She then downloads and reduces the data

I suspect you have answered your own question here. If other people
could be changing the data she is working on there is no other way.

If that is not what you intend then she should be working on a
timestamped snapshot clone copy of the data on her own PC.

Regards,
Martin Brown