How to hide multiple rows on a specific worksheet, dependent upon if data is entered into a specific column

Question

I have been using a VBA script that has worked well but has bogged down the systems because it refreshes every time you activate the worksheet.

Private Sub Worksheet_Activate()

StartRow = 18 

EndRow = 817 

ColNum = 2 

For i = StartRow To EndRow 

Cells(i, ColNum).EntireRow.Hidden = False 

If Cells(i, ColNum).Value = " " Then 

    Cells(i, ColNum).EntireRow.Hidden = True 

Else 

    Cells(i, ColNum).EntireRow.Hidden = False 

End If 

Next i

End Sub

I am new to using the Code Editor in Microsoft Office 365 so I am trying to figure out how to convert this. I have searched but cannot find anything that provides the same function.

Andrew Blackwell

Answer 1

This should be faster:

Private Sub Worksheet_Activate()
    Const StartRow = 18
    Const EndRow = 817
    Const ColNum = 2
    Dim i As Long
    Dim rng As Range
    Application.ScreenUpdating = False
    Range(Cells(StartRow, ColNum), Cells(EndRow, ColNum)).EntireRow.Hidden = False
    For i = StartRow To EndRow
        If Cells(i, ColNum).Value = "" Then
            If rng Is Nothing Then
                Set rng = Cells(i, ColNum)
            Else
                Set rng = Union(Cells(i, ColNum), rng)
            End If
        End If
    Next i
    If Not rng Is Nothing Then
        rng.EntireRow.Hidden = True
    End If
    Application.ScreenUpdating = True
End Sub