Excel 2003: VBA - Hide Rows process now too slow

  • Thread starter Thread starter BEEJAY
  • Start date Start date
B

BEEJAY

Have used the following for a number years (Thanks to Frank Kabel)
However, as the pricing sheets get larger (now about 1500 lines), it is
taking too long from start to finish. This particular process takes about 1
full minute.
The area of concern in the coding shown is between the >>>>>>>>>>>
Any ideas/suggestions would be greatly appreciated

Sub PrintPricingCustomer()
' PrintPricingCustomer Macro
' Keyboard Shortcut: NONE
Worksheets("Pricing").Select
Call WS_Unprotect
Application.ScreenUpdating = False
Cells.Select
With Selection
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Interior.ColorIndex = -4142
End With
With ActiveSheet.PageSetup
.PrintGridlines = False
.CenterHorizontally = True
End With
Cells.Select
With Selection
.EntireRow.Hidden = False
.EntireColumn.Hidden = False
End WithDim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "x" Then
Rows(RowNdx).Hidden = True
End If
Next RowNdx
Columns("F:G").Select
Selection.EntireColumn.Hidden = True
Worksheets("Pricing").PrintOut Copies:=1, Collate:=True

Call Green
Call WS_Protect
Application.ScreenUpdating = True
End Sub
 
If you turn off the page break lines
tools|options|view tab|uncheck page breaks (in xl2003)

Does the code work faster?

When you're hiding rows/columns, excel wants to figure out where to draw those
lines each time you change the layout.

Turning of calculation and making sure you're in Normal view will help, too.

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

'your code here

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

End Sub
 
Dave: Did as suggested. Made no time difference.
Started moving various "parts" of code around, commenting out, etc.
Suddenly, the time dropped from about 1 min. 10seconds, to 9 seconds.
Hurray.

In the process, I tried to insert a timer, so I wouldn't have to use my
stop-watch.
However, it comes up with a time of 51,555.7 on an approx. 8 - 9 second run.
What gives? Extensive googling seems to indicate that the code should be OK.
CODE:
Dim StartTime, EndTime As Date

StartTime = Time

my code.........................................

EndTime = Timer
' Display Total Time in Seconds
MsgBox Format(EndTime - StartTime, "0.0")

Your guidance would be appreciated, as always.
 
Dim iCtr As Long
Dim StartTime As Date, EndTime As Date

StartTime = Now
For iCtr = 1 To 100000
Debug.Print iCtr
Next iCtr
EndTime = Now

MsgBox Format(EndTime - StartTime, "hh:mm:ss")
 
In your For look build up a variable that looks something like
hideRows below - where the numbers correspond to the rows to be
hidden:

Dim hideRows as String
hideRows = "17:17,18:18,28:28"

'Then

sheet.Range(hideRows).EntireRow.Hidden = True

This is guaranteed to provide a dramatic improvement in performance -
but you get a 400 error if you try to hide more than 40 rows at a time
- so you may have to modify your For loop for that.
 
Dave, Thanks much.

Dave Peterson said:
Dim iCtr As Long
Dim StartTime As Date, EndTime As Date

StartTime = Now
For iCtr = 1 To 100000
Debug.Print iCtr
Next iCtr
EndTime = Now

MsgBox Format(EndTime - StartTime, "hh:mm:ss")
Click to expand...
 
Thanks for your response. I will try to understand it and work it thru.
For now (pressed for time) since I'm under 10 Seconds, its on "hold".
Anyway, I do appreciate this. It's all part of the learning curve.
 
Back
Top