Speed up VBA

[GalvinGreene]

So I have written some code that is meant to hide and unhide rows directly beneath the cell I am changing from OPEN to CLOSE and back again. The only thing is it is painfully slow doing that. Which I wouldn't mind if it wasn't also painfully slow entering in data in the sheet...

Is there anyway to speed this up?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iRow As Integer
Dim j As Integer
Dim k As Integer
Dim iCol As Integer

iCol = ActiveCell.Column
iRow = ActiveCell.Row

If iCol = 15 Then

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

j = iRow + 1
k = iRow + 3
If iCol = 15 Then
    If iRow >= 12 Then
        If iRow < Range("W4").Value Then
            If Round(iRow / 4, 0) = iRow / 4 Then
                If ActiveCell.Value = "CLOSE" Then
                    Range(j & ":" & k).EntireRow.Hidden = True
                Else
                    Range(j & ":" & k).EntireRow.Hidden = False
                End If
            Else
            End If
        Else
        End If
    Else
    End If
Else
End If
End If

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


End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

It is unusual to be basing actions off ActiveCell in a Worksheet_Change event code. The reason is, when entering a value in a cell you can complete that entry by pressing Enter, pressing Tab, clicking anywhere with the mouse which means even though you have changed, say, O20 the active cell could be anything.

I'm not sure it is related to your issue or even the results of your code, but what happens if you change the three places in your code where you have ActiveCell to Target since Target is the argument feeding automatically into your event code?

Private Sub Worksheet_Change(ByVal Target As Range)

 

[Alex Blakenburg]

I am not sure why your code is running that slowly. It might be a purely a matter of matter of having a large number of rows under where you are hiding / unhiding rows.

Here is a slightly cleaned tidier version of your code with Peter's modifications included.

One thing that might be worth trying is to actually comment out the 2 Application.Calculation lines.
I don't think hiding rows would cause that much in terms of calculations and the act of turning it back on might actually result it more calculations then just leaving it on., especially if you have multiple workbooks open at the same time. Anyway it is the only thing I can think of that might help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iRow As Integer
Dim j As Integer
Dim k As Integer
Dim iCol As Integer

iCol = Target.Column
iRow = Target.Row

If iCol = 15 Then
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    
    j = iRow + 1
    k = iRow + 3
    
        If iRow >= 12 And iRow < Range("W4").Value And iRow Mod 4 = 0 Then
                    If Target.Value = "CLOSE" Then
                        Range(j & ":" & k).EntireRow.Hidden = True
                    Else
                        Range(j & ":" & k).EntireRow.Hidden = False
                    End If
        End If
        
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End If

 

[Peter_SSs]

Here is a slightly cleaned tidier version of your code ..

Alex, I think completely unrelated to the OP's overall issue, but this 'tidier' code ...

If iRow >= 12 And iRow < Range("W4").Value And iRow Mod 4 = 0 Then

would actually be marginally slower than the original 'untidier' code

If iRow >= 12 Then
        If iRow < Range("W4").Value Then
            If Round(iRow / 4, 0) = iRow / 4 Then

In your code 3 calculations/comparisons have to be made every time through this loop.
In the original code if the first comparison code fails, then it is the only test required.
If the first comparison passes but the second fails, then only two calculation/comparisons need to be made.

In the above two scenarios then there is a slight time-saving with the original code structure

 

[Alex Blakenburg]

@Peter_SSs, I had considered that to be a possibility. I figured leaving the column check separate would partially mitigate that since it would knock out the majority of the unwanted changes. Also it was duplicating the column check and performing it twice.
I don’t know if you have a suggestion for speeding up the code. The only thing I could come up with as worth a try was to leave automatic calculation turned on and not turn it off and back on in the code.

 

[offthelip]

I would suggest the code doens't need any of these all of which will take abit of time:

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

 

[GalvinGreene]

Peter

Welcome to the MrExcel board!

It is unusual to be basing actions off ActiveCell in a Worksheet_Change event code. The reason is, when entering a value in a cell you can complete that entry by pressing Enter, pressing Tab, clicking anywhere with the mouse which means even though you have changed, say, O20 the active cell could be anything.

I'm not sure it is related to your issue or even the results of your code, but what happens if you change the three places in your code where you have ActiveCell to Target since Target is the argument feeding automatically into your event code?

Private Sub Worksheet_Change(ByVal Target As Range)

So I had tried using Target, but that seemed to keep throwing some error when grabbing the value. Thus the reason there is the garbled mess of ActiveCell references.
But I tried again and that does seem to give it some speed!

It seems another issue, I was accessing the remote file instead of the local file.

Also removing the updating sections made data entry much smoother.

Thanks all!