Hiding row if cell equals next visible cell

Question

I am trying to write a macro that hides the row if the cell value equals the next visible cell in that column and loops through the whole column. I have read that SpecialCells(xlCellTypeVisible) only works up to 8192 cells and my spreadsheet has 15,000 rows.

I have tried something like this but want to restrict it to only visible cells

Sub Test()
For i = 7 To 15258
    If Range("P" & i).Value = Range("P" & i + 1).Value Then
        Rows(i).Hidden = True
    End If
Next i
End Sub

I have tried to search for a solution but haven't been able to find one yet.

Thanks!

Answer 1

I'd be surprised if this couldn't be optimized just a little bit, but it will work for what you are needing.

You can follow the comments within the code itself to kind of get a sense of what it's doing, but in a nutshell, you are using a For...Next statement to loop through your visible cells. For each visible cell, you will search for the next visible cell and then check to see if that matches. If it does, you add that cell to a special range that tracks all the rows to hide at the end of the code, then hide it.

Sub Test()

    Dim ws As Worksheet, lookupRng As Range, rng As Range, lstRow As Long
    Set ws = ThisWorkbook.Worksheets(1)
    lstRow = 15258
    Set lookupRng = ws.Range("P7:P" & lstRow)

    Dim rngToHide As Range, i As Long
    For Each rng In lookupRng.SpecialCells(xlCellTypeVisible)
        Application.StatusBar = "Checking row " & rng.Row & " for matches."
        For i = rng.Row + 1 To lstRow                   'Loop through rows after rng
            If Not ws.Rows(i).Hidden Then               'Check if row is hidden
                If rng.Value = ws.Cells(i, "P") Then    'check if the non-hidden row matches
                    If rngToHide Is Nothing Then        'Add to special range to hide cells
                        Set rngToHide = ws.Cells(i, "P")
                    Else
                        Set rngToHide = Union(rngToHide, ws.Cells(i, "P"))
                    End If
                End If
                Exit For                                'Exit the second For statement
            End If
        Next i
    Next rng

    Application.StatusBar = "Hiding duplicate rows"
    If Not rngToHide Is Nothing Then rngToHide.EntireRow.Hidden = True
    Application.StatusBar = False

End Sub