Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro SLOW for coworker but works fine for me.

0

Hi Everyone,

I have an annoying problem.  At my work, we use an Excel template that uses various rows with fields that can be unhidden if the user needs.  The VBA code uses a loop and the Worksheet_Change functionality (it's slow, and not my code - I need to redo it).  Nevertheless, the code runs fine for me and various other coworkes.  However, one coworker experiences 10-20 seconds of annoying delay after every entry in the template.  It seems the code takes way too long to run through its loop.  Any ideas?  I've checked that we are using the same code and the same version of Excel.

If needed, here is the code.  Column A's cells all have an If formula that defaults to value 0, but changes to 1 if the user changes certain other fields to add the rows Example formula =IF(D50="Y", 1, 0) 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    Application.Calculation = xlCalculationManual
        For Each xRg In Range("A1:A461")
            If xRg.Value = "0" Then
                xRg.EntireRow.Hidden = True

            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Thank you so much for any ideas!

excel
posted by: nbrooks44 (rep: 2) on Mon Aug 7, 2023 at 2:14 pm
Answer
Discuss

Answers

1
Selected Answer

Hi and welcome to the Forum

Not sure why you say the code is slow- it works in a fraction of a second on my 10-year old PC!

In the case that the formula in column A is as simple as your example formula of (for A50):

=IF(D50="Y", 1, 0) 

then you could try the changes in bold below, the first of which means the code runs just the first line unless the change was in column D:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' do nothing unless a value in the dependent range changes
    If Intersect(Target, Range("D1:D461")) Is Nothing Then Exit Sub

    Dim xRg As Range
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    Application.Calculation = xlCalculationManual
        For Each xRg In Range("A1:A461")
            If xRg.Value = "0" Then
                xRg.EntireRow.Hidden = True
            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = False

End Sub

Hope this helps- if so, please remember to mark this Answer as Selected. If not, please edit your question to either add a representative Excel file or shows the actual formula.

posted by: John_Ru (rep: 6152) on Mon Aug 7, 2023 at 4:44 pm
Discuss

Discussion

This worked, thank you!   Interestingly, after running the code with the EnableEvents lines you added, the coworker's computer could run the old code just fine too!  At any rate, I added those lines to the main template so it hopefully won't happen again.
nbrooks44 (rep: 2) Aug 7, '23 at 5:37 pm
Great. Thanks for selecting my Answer.
John_Ru (rep: 6152) Aug 7, '23 at 5:43 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login