How can I improve performance in an Excel VBA For loop with nested If loop?

excel vba for loop two variables
nested for each loop vba
excel vba =if statement multiple conditions
for loop vba
vba for each loop
excel vba if then multiple statements
excel vba loop through rows
if statement vba

This is my first post, so if I need to make any changes, please let me know.

I've found a few examples of ways to optimize loops, but I cannot seem to apply any of them effectively to my code. What I'm trying to do is loop through about 170 cells in a single column, and hide or show the entire row based on whether the cell value is 0 or not.

I want the code to run each time I activate certain sheets. Right now this piece of code is taking about 4 seconds to run. It seems like it should be much faster than that! That's why I'm here for help.

Here is the code I'm using (FormatSheet returns a BOOLEAN where True means that it is okay to perform this code on this sheet and False means to skip performing this code on this sheet:

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

With several very slight changes:

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

run quite rapidly

VBA If Else Statement, Building a VBA If Else statement is quite similar to building a nested if formula inside Excel. The benefit of using VBA instead is that it's much easier to follow,  Use these additional VBA performance optimizations. The following is a list of additional performance optimizations you can use in your VBA code: Return results by assigning an array directly to a Range. Declare variables with explicit types to avoid the overhead of determining the data type, possibly multiple times in a loop, during code


One thing that may speed up it a little bit.. I would not use z variable. You can do the same with existing r.

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

Help with a VBA Loop with Nested If statement, Help with a VBA Loop with Nested If statement uses a lot of VLookups Nested inside of If Statements and the calculation speed is horrible. Can someone help provide me a better way via VBA to accomplish the same goal? I recorded a Macro and added the formulas that are currently in the excel sheet. There are many different articles and post on improving Excel performance. What I personally was missing was a simple, comprehensive, short overview of how the performance of VBA macros can be improved without needing to read through long articles on how the Excel compiler works.


I think the quickest way could be to use autofilter. Set the autofilter with values <> 0 and voilà!

VBA For Loop - A Complete Guide, If you are looking for information about the VBA While and VBA Do Loop then go 7.3 Using the VBA For Each Loop With Arrays; 7.4 Using Nested For Each Loops Immediate Window before then this section will get you up to speed quickly. If you were to increase the number fruit items to a large value like 10,000 then  Next [Counter] – Closing statement to the For Next Loop. You can include the Counter or not. However, I strongly recommend including the counter as it makes your code easier to read. If that’s confusing, don’t worry. We will review some examples: Count to 10. This code will count to 10 using a For-Next Loop:


Excel VBA Loops: For Next, Do While, Do Until, For Each (with , You can use a VBA loop to go through the range and analyze each cell row Let's have a look at a few examples to better understand how For Next loop works. When you use the 'Exit For' statement within a nested 'For Next' loop, it will  While looking into Excel VBA performance issues I learned that some desktop search tools implement event listeners (probably to better track document contents as it changes). You might not want Excel firing an event for every cell you’re changing via code, and turning off events will speed up your VBA code performance if there is a COM Add-In


Optimize loops, Speed up the loops; Speed up operations on data; Optimize the control flow It makes sense if the loop is nested inside another loop and execution enters the  In VBA, you can loop through a range of cells, applying actions to each cell in the range. If you want to test a condition for each cell in a range using VBA, the best way is to loop through the range, testing each cell. Here are two code examples to demonstrate how to loop through a range.


Using IfThenElse statements (VBA), Access · Excel · Kaizala · Microsoft Teams · OneDrive · OneNote IfThenElse statements can be nested to as many levels as you To run only one statement when a condition is True, use the Function Bonus(performance, salary) If performance = 1 Then Bonus = salary * 0.1 ElseIf performance = 2  When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For i statement. For i = 2 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc. Triple Loop. You can use a triple loop to loop through two-dimensional ranges on multiple Excel worksheets.