R

Post the code.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"RSnyder" wrote in message
...
I have a VB instruction set (macro) that hides unnecessary lines on a
worksheet before printing out the worksheet and it works fine except for
when I repeat the macro each time after that. It's not that the macro
instructions aren't carried out it's that the time it takes to execute slows
down dramatically after the 1st execution of the macro. Typically, the code
executes and does the print the 1st time in roughly 3-4 seconds. Its
roughly 270 lines for the IF statements to determine the hide status on a
line and to do the hide or proceed to the next line. All executions after
the initial execution the time jumps up dramatically to 15 seconds. The
speed on each successive execution looks to be about the same. Now that's
not a big deal either however me being a speed demon I'm searching for speed
and not "that's acceptable".

Help me out here. Is this a possible variable issue not being released
after the 1st execution is done even though I'm completely out of the macro
or is it something else maybe in the operating environment? If I closed out
of the workbook and came back in each time I get the same result when
running the macro.

Here's the cod

Dim NextLine As Rang
Dim CurrLine As Rang
Dim RptKey As Intege
Dim NumCopies As Intege


With Applicatio
.Calculation = xlManua
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals

RptKey = InputBox(
Prompt:="Choose Sale Rpt Base Type; 1-Net Whse Sales, 2-Net Total Sales, 3-Period Variance Rpt, 4-Quarter Variance Rpt",
Title:="Report Sales Base Type",
Default:=1
NumCopies =
NumCopies = InputBox(
Prompt:="How Many Copies Do You Need Printed? (Maximum of 5)",
Title:="NUMBER OF REPORTS TO PRINT",
Default:=1
If NumCopies 5 The
MsgBox ("The number of copies to print is excessive, 1 will be printed. Use the copier for multiples."
NumCopies =
End I
If RptKey < "1" Or RptKey "4" The
MsgBox ("Your slection must be 1, 2, 3 or 4; you have selected an invalid key!"
ElseIf RptKey = "1" Then 'Report based on net Whse sale
Range("FLD_CHK_TOPRINT_WNS").Selec
ActiveSheet.Unprotec
Set CurrLine = Range("FLD_CHK_TOPRINT_WNS"
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireRow.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
Loop Until CurrLine.Value = "XX
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=Tru
Cells.Selec
Selection.EntireRow.Hidden = Fals
Range("FLD_DATACENTER").Selec
With Applicatio
.Calculation = xlAutomati
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
ActiveSheet.Protect "", True, True, , Tru
ElseIf RptKey = "2" Then 'Report based on net Total sale
Range("FLD_CHK_TOPRINT_TNS").Selec
ActiveSheet.Unprotec
Set CurrLine = Range("FLD_CHK_TOPRINT_TNS"
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireRow.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
Loop Until CurrLine.Value = "XX
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=Tru
Cells.Selec
Selection.EntireRow.Hidden = Fals
Range("FLD_DATACENTER").Selec
With Applicatio
.Calculation = xlAutomati
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
ActiveSheet.Protect "", True, True, , Tru
ElseIf RptKey = "3" Then 'Period Variance Repor
Range("FLD_CHK_TOPRINT_PVRSRow").Selec
ActiveSheet.Unprotec
Set CurrLine = Range("FLD_CHK_TOPRINT_PVRSRow"
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireRow.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
Loop Until CurrLine.Value = "XX
Set CurrLine = Range("FLD_CHK_TOPRINT_PVRSRow"
Set NextLine = CurrLine.Offset(1, 0
Range("FLD_CHK_TOPRINT_PVRSCol").Selec
Set CurrLine = Range("FLD_CHK_TOPRINT_PVRSCol"
Set NextLine = CurrLine.Offset(0, 1
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireColumn.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(0, 1
CurrLine.Selec
Loop Until CurrLine.Value = "XX
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=Tru
Cells.Selec
Selection.EntireRow.Hidden = Fals
Cells.Selec
Selection.EntireColumn.Hidden = Fals
Range("FLD_DATACENTER").Selec
With Applicatio
.Calculation = xlAutomati
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
ActiveSheet.Protect "", True, True, , Tru
ElseIf RptKey = "4" Then 'Quarter Variance Repor
ActiveWorkbook.Sheets("VR Qtr Stmt").Select
ActiveSheet.Unprotect
Range("FLD_CHK_TOPRINT_QVRSRow").Select
Set CurrLine = Range("FLD_CHK_TOPRINT_QVRSRow")
Set NextLine = CurrLine.Offset(1, 0)
CurrLine.Select
Do
If CurrLine.Value = "N" Then
Selection.EntireRow.Hidden = True
End If
Set CurrLine = NextLine
Set NextLine = CurrLine.Offset(1, 0)
CurrLine.Select
Loop Until CurrLine.Value = "XX"
Set CurrLine = Range("FLD_CHK_TOPRINT_QVRSRow")
Set NextLine = CurrLine.Offset(1, 0)
Range("FLD_CHK_TOPRINT_QVRSCol").Select
Set CurrLine = Range("FLD_CHK_TOPRINT_QVRSCol")
Set NextLine = CurrLine.Offset(0, 1)
CurrLine.Select
Do
If CurrLine.Value = "N" Then
Selection.EntireColumn.Hidden = True
End If
Set CurrLine = NextLine
Set NextLine = CurrLine.Offset(0, 1)
CurrLine.Select
Loop Until CurrLine.Value = "XX"
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=True
Cells.Select
Selection.EntireRow.Hidden = False
Cells.Select
Selection.EntireColumn.Hidden = False
Range("FLD_DATACENTER").Select
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Protect "", True, True, , True
End If
End Sub


----- Dick Kusleika wrote: -----

R

Post the code.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"RSnyder" wrote in message
...
I have a VB instruction set (macro) that hides unnecessary lines on a
worksheet before printing out the worksheet and it works fine except for
when I repeat the macro each time after that. It's not that the macro
instructions aren't carried out it's that the time it takes to execute slows
down dramatically after the 1st execution of the macro. Typically, the code
executes and does the print the 1st time in roughly 3-4 seconds. Its
roughly 270 lines for the IF statements to determine the hide status on a
line and to do the hide or proceed to the next line. All executions after
the initial execution the time jumps up dramatically to 15 seconds. The
speed on each successive execution looks to be about the same. Now that's
not a big deal either however me being a speed demon I'm searching for speed
and not "that's acceptable".
Help me out here. Is this a possible variable issue not being released
after the 1st execution is done even though I'm completely out of the macro
or is it something else maybe in the operating environment? If I closed out
of the workbook and came back in each time I get the same result when
running the macro.

R

The first thing you need to do is make your code as efficient as possible.
I would think that inefficient code would be slow all the time, not just
after the first time, but by streamlining your code, you can remove that as
a possible problem.

Here are some comments:

Dim all your integer variables as Long. Internally, VBA only uses Longs so
it's converting them for you.

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

You set the MaxChange, then set it to the exact same value at the end. It
would seem that you don't need either of these lines, but for sure you
don't need one of them. Setting PrecisionAsDisplayed to False doesn't do
much for you. If it was set to True, the precision is lost forever, so
setting it to False changes nothing. You may want it to be False, but this
macro probably isn't the place to do it.

'
RptKey = InputBox( _
Prompt:="Choose Sale Rpt Base Type; 1-Net Whse Sales, 2-Net Total
Sales, 3-Period Variance Rpt, 4-Quarter Variance Rpt", _
Title:="Report Sales Base Type", _
Default:=1)
NumCopies = 1

This line is unnecessary. NumCopies will either be changed via the InputBox
or the If block that follows it.

NumCopies = InputBox( _
Prompt:="How Many Copies Do You Need Printed? (Maximum of 5)", _
Title:="NUMBER OF REPORTS TO PRINT", _
Default:=1)

You might consider using Application.InputBox instead of just InputBox. You
can specifiy a Type argument that will force the users to enter a number.
The way you have it now, the result is always a string. VBA is doing some
conversions for you, whcih is fine, but it slows things down.

If NumCopies 5 Then
MsgBox ("The number of copies to print is excessive, 1 will be printed.
Use the copier for multiples.")
NumCopies = 1
End If
If RptKey < "1" Or RptKey "4" Then

When you change to Application.InputBox, you won't need the quotes around
these.

MsgBox ("Your slection must be 1, 2, 3 or 4; you have selected an
invalid key!")
ElseIf RptKey = "1" Then 'Report based on net Whse sales
Range("FLD_CHK_TOPRINT_WNS").Select

This will be the biggest time savings. Anytime you Select or Activate
anything, it takes time. You should work directly with the objects instead
of selecting them and working with the Selection object.

You should also make Application.ScreenUpdating = False at the beginning of
your sub. Hiding rows updates the screen and will slow things down.

I think making the code more efficient will be good even if it doesn't solve
the problem. If you like, you can send me the workbook and I can make some
specific recommendations for rewriting it. If you can't do that, I will use
the code that's here. It's a big job, though, so I won't be able to get it
done today.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com