如何优化特定的VBA代码速度? [英] How to optimize specific vba code speed?

问题描述

我们使用此VBA代码主要是为了隐藏空白行&取消隐藏非空白行，此后，一旦工作表激活，第二个代码将按定义的列值对行进行排序.使用此代码会花费很多时间，请问有人可以帮助我优化此代码并使其更快吗?(工作表平均包含500行).

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

推荐答案

尝试一下:

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

这篇关于如何优化特定的VBA代码速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！