Search code examples
excelvbaloopshide

Slow VBA loop for hiding rows


Following code used to loop through all rows in a range and hide them based on cell value in that cell, and one below. If both cell values = "" then intent is to hide entire row. All works fine but is terribly slow. Any advice on something faster would be greatly appreciated.

Sheets("Morning Report Export Sheet").Activate

For x = 10 To 108
    If Cells(x, 9).Value = "" Then
        If Cells(x + 1, 9).Value = "" Then
            Cells(x, 9).EntireRow.Hidden = True
        End If
    End If
Next

... Have also tried the following, but just as slow...

If Cells(x, 9).Value = "" And Cells(x + 1, 9).Value = "" Then

Solution

  • I wrote up both versions because I am bored.

    This is the array method mentioned by @MathieuGuindon

    Sub HideRowsUsingArrays()
        Dim x As Long, HideRows As Range
        Dim StartRow As Long, EndRow As Long, Col As Long
        
        'TARGET RANGE
        Col = 9
        StartRow = 10
        EndRow = 108
        'TARGET RANGE
        
        Dim sh As Worksheet
        Set sh = Sheets("Morning Report Export Sheet")
        
        Dim vArr() As Variant
        'Saving all values in the target range to an array
        vArr = sh.Cells(StartRow, Col).Resize(EndRow).Value
        
        'Looping through the array
        For x = LBound(vArr) To UBound(vArr) - 1
            'If val or next val is empty
            If vArr(x) = "" And vArr(x + 1) = "" Then
                'Add the corresponding row to HideRows range
                'Union causes an error if HideRows is nothing, so the first iteration cant use Union
                If HideRows Is Nothing Then
                    Set HideRows = sh.Rows(x + StartRow - 1).EntireRow
                Else
                    Set HideRows = Union(HideRows, sh.Rows(x + StartRow - 1).EntireRow)
                End If
            End If
        Next x
        
        'Hide the gathered rows
        If Not HideRows Is Nothing Then HideRows.EntireRow.Hidden = True
    End Sub
    

    This is the Range method linked to by @TimWilliams

    Sub HideRowsUsingRanges()
        Dim cell As Range, HideRows As Range
        Dim StartRow As Long, EndRow As Long, Col As Long
        
        'TARGET RANGE
        Col = 9
        StartRow = 10
        EndRow = 108
        'TARGET RANGE
        
        Dim sh As Worksheet
        Set sh = Sheets("Morning Report Export Sheet")
        
        Dim r As Range
        'Saving the target range
        set r = sh.Cells(StartRow, Col).Resize(EndRow)
        
        'Looping through each cell of the range
        For Each cell In r
            'If val or next val is empty
            If cell.Value = "" And cell.Offset(1).Value = "" Then
                'Add the corresponding row to HideRows range
                'Union causes an error if HideRows is nothing, so the first iteration cant use Union
                If HideRows Is Nothing Then
                    Set HideRows = cell
                Else
                    Set HideRows = Union(HideRows, cell)
                End If
            End If
        Next cell
        
        'Hide the gathered rows
        If Not HideRows Is Nothing Then HideRows.EntireRow.Hidden = True
    End Sub