How can I improve performance in an Excel VBA For loop with nested If loop?
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.