How to optimize specific vba code speed?

Question

we used this VBA code mainly to hide blank rows & unhide non-blank rows, after that the second code sort the rows by a defined column value once the worksheet activated. This process take too much time with this code, could any one help me optimize this code and make it faster? (the worksheet contain an average of 500 rows).

Private Sub Worksheet_Activate()
HideRows
Sortingrisk
End Sub

Sub HideRows()
Dim rRange As Range, rCell As Range
Dim strVal As String

   Set rRange = Worksheets(12).Range("A10:A500")

    For Each rCell In rRange
      strVal = rCell
      rCell.EntireRow.Hidden = strVal = vbNullString
    Next rCell
End Sub

Sub Sortingrisk()

    ActiveWorkbook.Worksheets("Control Implementation Plan").AutoFilter.Sort. _
      SortFields.Clear
   ActiveWorkbook.Worksheets("Control Implementation Plan").AutoFilter.Sort. _
      SortFields.Add Key:=Range("G10:G1000"), SortOn:=xlSortOnValues, Order:= _
      xlDescending, DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Control Implementation Plan").AutoFilter.Sort
     .Header = xlYes
      .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
    End With
End Sub

What du you call "too much time" ? it look really simple and shouldn't take more then a second — Maxime Porté
– Maxime Porté, Commented Aug 10, 2015 at 11:48
Yeah, I do the test on 4k lines, it takes a while. Try with the solution I gave, it was far better — Maxime Porté
– Maxime Porté, Commented Aug 10, 2015 at 12:19

aLearningLady · Accepted Answer · 2015-08-10 13:00:51Z

1

Insert this at the start of your Sub:

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

And this just before End Sub:

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

answered Aug 10, 2015 at 13:00

aLearningLady

2,0984 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Excel Developers · Accepted Answer · 2015-08-10 14:02:40Z

1

Try this:

Worksheets(12).Range("A10:A500").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

answered Aug 10, 2015 at 14:02

Excel Developers

2,8352 gold badges24 silver badges36 bronze badges

Comments

Maxime Porté · Accepted Answer · 2015-08-10 11:56:49Z

0

Your HiddenRows take an eternity. Try with

Sub HideRows()
    Worksheets(12).Range("A10:A500").Hidden = True
End Sub

answered Aug 10, 2015 at 11:56

Maxime Porté

1,0948 silver badges13 bronze badges

Comments

u8it · Accepted Answer · 2015-08-10 15:18:17Z

From a programming perspective, you should hide your entire range without using a loop. You can also optimize the run-time environment, with application properties being the first place to start.

Usually

Application.ScreenUpdating = False

is the most important line for speeding up a macro that's manipulating spreadsheet content.

Followed by

Application.Calculation = xlCalculationManual

which can be useful if your macro is triggering recalculation. I always hesitate to alter the calculation state from automatic though, since you risk leaving you spreadsheet in manual mode if the macro fails, and that can be very dangerous, especially if someone else who doesn't know about the macro is using it.

I would not disable DisplayStatusBar or EnableEvents. You stand very little to gain as far as speed and a lot to loose as far as functionality.

Here is an example of your code streamlined a little more and using a manual calculation state that will safely reset back to auto on a non-fatal error. You may want to consider removing the manual state or constructing additional error handling.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
HideRows Me
SortingRisk Range("G10:G1000")
End Sub
Sub HideRows(ByRef w As Worksheet)
w.Range("A10:A500").Rows.Hidden = True
End Sub
Sub SortingRisk2(ByRef R As Range)
Application.Calculation = xlCalculationManual
On Error GoTo term
Dim F As AutoFilter
With R.Worksheet
    If .AutoFilter Is Nothing Then
        R.AutoFilter
    End If
    Set F = R.Worksheet.AutoFilter
    F.Sort.SortFields.Clear
End With

With F.Sort
    .SortFields.Add _
       Key:=R, _
       SortOn:=xlSortOnValues, _
       Order:=xlDescending, _
       DataOption:=xlSortNormal
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

term:
Application.Calculation = xlAutomatic
If Err > 0 Then
    MsgBox "Error: Macro has terminated. Verify that Workbook Calculation
state is in auto."
End If
End Sub

Collectives™ on Stack Overflow

How to optimize specific vba code speed?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related