Why does this simple macro (to hide rows) cause Excel to become unresponsive?

Question

As part of a recent Transport Science project, I've been given a sheet with data from 7094 car crashes. In an attempt to filter out only the relevant data - in this case crashes involving pedestrians, fatalities or serious injuries - I tried adapting a macro I found online.

This is my first time dabbling in VBA, although I have some undergrad experience in C and Java (just in case this proves relevant somehow). The code is as follows:

Sub HideRows()
BeginRow = 2
EndRow = 7095
ChkCol = 10

For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value > 0 Or Cells(RowCnt, ChkCol + 1).Value > 0 Or Cells(RowCnt, ChkCol + 2).Value > 0 Then
        Rows(RowCnt).EntireRow.Hidden = False
    Else
        Rows(RowCnt).EntireRow.Hidden = True
    End If
        Next RowCnt
End Sub

The problem is it causes Excel to become unresponsive. I can see the macro is performing the intended function, but I can't save or regain control over the program at the end.

Struggling with this is wasting a lot of time and I've got a feeling the problem (and subsequent fix) is very, very simple - hopefully.

Any advice will be greatly appreciated.

Answer 1

In addition to adding ScreenUpdating and EnableEvent booleans you can also refactor the code to only perform one hide / unhide operation (or two in this case), instead of doing it on each loop iteration, which will slow things down. Also you can turn off calculations (in case that affects things).

Option Explicit

    Sub HideRows()

        Dim BeginRow As Integer, EndRow As Integer, ChkCol As Integer

        BeginRow = 2
        EndRow = 7095
        ChkCol = 10

        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With

        Application.Calculation xl

        Dim rHide As Range
        Dim rShow As Range


        For RowCnt = BeginRow To EndRow

            If Cells(RowCnt, ChkCol).Value > 0 Or Cells(RowCnt, ChkCol + 1).Value > 0 Or Cells(RowCnt, ChkCol + 2).Value > 0 Then

                If Not rHide Is Nothing Then
                    Set rHide = Cells(1, RowCnt)
                Else
                    Set rHide = Union(rHide, Cells(1, RowCnt))
                End If

            Else

                If Not rShow Is Nothing Then
                    Set rShow = Cells(1, RowCnt)
                Else
                    Set rShow = rShow(rHide, Cells(1, RowCnt))
                End If

            End If

        Next RowCnt

        'show / hide appropriate ranges
        rHide.EntireRow.Visible = False
        rShow.EntireRow.Visible = True

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With

    End Sub

Answer 2

To speed this code up, you simply need to add Application.ScreenUpdating = False to the start and Application.ScreenUpdating = True at the end

Sub HideRows()
BeginRow = 2
EndRow = 7095
ChkCol = 10

Application.ScreenUpdating = False
For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value > 0 Or Cells(RowCnt, ChkCol + 1).Value > 0 Or Cells(RowCnt, ChkCol + 2).Value > 0 Then
        Rows(RowCnt).EntireRow.Hidden = False
    Else
        Rows(RowCnt).EntireRow.Hidden = True
    End If
Next RowCnt
Application.ScreenUpdating = True
End Sub

Now, you may also have some events or conditional formats that trigger each time the sheet is updated. If so, also include Application.EnableEvents = False at the beginning and turn them back on at the end of the loop.

And if you really wanted, you could simplify your Boolean checks by simply saying:

If Cells(RowCnt, ChkCol).Value Or Cells(RowCnt, ChkCol + 1).Value Or Cells(RowCnt, ChkCol + 2).Value 0 Then

because 0=False in VBA. This really shouldn't be necessary however and your way is certainly easier to read.