Hide and un-hide rows then sort on Worksheet_Activate event 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
There are two things that I think can easily be done.


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 &amp; .Rows.Count).End(xlUp).Row
    End With

End Function

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.