Loop Through Visible Data Only With Range

Question

I've looked around and found similar items but I just can't seem to put them to use. I have quite a simple code. The problem is simply checking if a row is hidden takes like 30 minutes to process as I'm using sometimes more than 500,000 rows.

After filtering down the data to about 100 I'd like to only be using this data so that I'm only computing 100 and not checking the other 499,900. Only what's on the screen. It's not working anymore as I'm still playing around with it but, here's my code:

Sub HideRows()
    i = 1

    Dim cl As Range

    For Each cl In Range(Cells(i, 8), Cells(Sheets(Wss).Range("H:H").End(xlDown).End(xlDown).End(xlUp).Row - 1, 1)).SpecialCells(xlCellTypeVisible)
        If Cells(i + 1, 8).Value = 1 And Cells(i, 8).Value = 1 And Cells(i + 1, 8).Row = Cells(i, 8).Row + 1 Then
            Cells(i + 1, 8).EntireRow.Hidden = True
        Else
            Cells(i + 1, 8).EntireRow.Hidden = False
        End If

        i = i + 1
    Next
End Sub

Answer 1

Try this out:

Sub HideRows()
    Const ROW_START As Long = 1
    
    Dim ws As Worksheet
    Dim c As Range, a As Range, rngVis As Range, bHide As Boolean
    
    Set ws = ActiveSheet 'or whatever
    On Error Resume Next
    Set rngVis = ws.Range(ws.Cells(ROW_START, "H"), ws.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If rngVis Is Nothing Then Exit Sub 'no visible rows
    
    For Each a In rngVis.Areas 'loop range areas
        bHide = False          'reset flag
        For Each c In a.Rows   'loop rows in area
             If c.Value = 1 Then
                If bHide Then c.EntireRow.Hidden = True 'second or later "1"  - hide row
                bHide = True   'set flag for later rows
             Else
                bHide = False  'unset flag for later rows
             End If
        Next c
    Next a
End Sub