Как улучшить производительность в цикле Excel VBA For с вложенным циклом If?

Question

Это мой первый пост, поэтому, если мне нужно внести какие-либо изменения, пожалуйста, дайте мне знать.

Я нашел несколько примеров способов оптимизации циклов, но я не могу применить какой-либо из них эффективно к моему коду. То, что я пытаюсь сделать, это перебрать около 170 ячеек в одном столбце и скрыть или показать всю строку в зависимости от того, равно ли значение ячейки 0 или нет.

Я хочу, чтобы код запускался каждый раз, когда я активирую определенные листы. Прямо сейчас этот кусок кода занимает около 4 секунд для запуска. Кажется, что это должно быть намного быстрее, чем это! Вот почему я здесь за помощью.

Вот код, который я использую (FormatSheet возвращает BOOLEAN, где True означает, что можно выполнить этот код на этом листе, а False - пропустить выполнение этого кода на этом листе:

Private Sub mobjWb_SheetActivate(ByVal Sh As Object)

Dim r As Long
Dim z As Long
Dim varray As Variant

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

If Not FormatSheet(Sh) Then
Exit Sub
End If

Set varray = Range("$F$1", Cells(Rows.count, "F").End(x1up)).Value


For Each r In varray

z = r.Value
If z = 0 Then
Range("F" & r).EntireRow.Hidden = True
    Else
    Range("F" & r).EntireRow.Hidden = False
End If
Next r


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


End Sub

Answer 1

С несколькими очень незначительными изменениями:

Sub qwerty()
   Dim r As Range
   Dim z As Long, N As Long
   Dim varray As Range

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

   N = Cells(Rows.Count, "F").End(xlUp).Row
   Set varray = Range("F1:F" & N)

   For Each r In varray
      z = r.Value
      If z = 0 Then
         r.EntireRow.Hidden = True
      Else
         r.EntireRow.Hidden = False
      End If
   Next r

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

End Sub

беги довольно быстро

Answer 2

Я думаю, что самый быстрый способ - использовать автофильтр. Установите для автофильтра значения <> 0 и вуаля!

Answer 3

Одна вещь, которая может ускорить это немного.. Я бы не использовал переменную z. Вы можете сделать то же самое с существующим р.

For Each r In varray
If r.Value = 0 Then
    Range("F" & r).EntireRow.Hidden = True
Else
    Range("F" & r).EntireRow.Hidden = False
End If
Next r