6
\$\begingroup\$

We used this VBA code mainly to hide blank rows and unhide non-blank rows. After that, the second code sorts the rows by a defined column value once the worksheet activates. This process takes too much time with this code.

Could anyone 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
\$\endgroup\$

1 Answer 1

5
\$\begingroup\$

There are two things that I think can easily be done.

  1. Find out where the data actually ends rather than always using row 500. Use a function like this:

    Function LastRowNum(sheetName As String, columnLetter As String) As Long
    
        With ThisWorkbook.Worksheets(sheetName)
            LastRowNum = .Range(columnLetter & .Rows.Count).End(xlUp).Row
        End With
    
    End Function
    
  2. If you are going to use an AutoFilter in the SortingRisk proc then why not use it to hide the empty rows? It should be much quicker than looping through cells using VBA.

\$\endgroup\$
3
  • \$\begingroup\$ Thanks @ChipsLetten for your quick reply and suggestions. Is the 'AutoFilter' able to unhide non-blank rows also ? \$\endgroup\$ Aug 10, 2015 at 11:44
  • \$\begingroup\$ It can do quite a lot. I suggest you turn on the macro recorder and play around with the AutoFilter. \$\endgroup\$ Aug 10, 2015 at 13:47
  • \$\begingroup\$ I provided a working example of the AutoFilter suggestion on the same question you asked on SO; it also shows the improvement in speed \$\endgroup\$
    – paul bica
    Aug 11, 2015 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.