Hide rows based on same values in cell

I have a large excel sheet and need to hide rows with similar values.
For every value in a specific cell one row should be kept and all others should be made hidden.

So at the end I only have a list of unique rows and no more duplicates.

This code assumes the data you want to compare in in column A, otherwise you'll need to change the 5 A's to whatever column you want.

Sub henk400()

For Each Cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If Cell.EntireRow.Hidden = False Then
        With Range("A:A")
        Set c = .Find(Cell.Value, After:=Cell, LookAt:=xlWhole)
        If Not c Is Nothing Then
            Do While Not c Is Nothing And c.Address <> Cell.Address
            c.EntireRow.Hidden = True
            Set c = .FindNext(c)
            Loop
        End If
        End With
    End If
Next

End Sub

Thank you for the code.
But when I execute it, I get the following error: Compile error: Variable not defined

When I close the message the first line is marked yellow: Sub henk400()

How do I need to solve this issue ?

Add this as the first line of code, directly after your Sub name, but before the For loop:

Dim Cell As Range, c As Range

Now I get: Run-time error '13': Type mismatch

the following line is marked yellow:

Set c = .Find(Cell.Value, After:=Cell, LookAt:=xlWhole)

Does it matter that the content of the cells are not numeric values ?

Can you post your workbook, or an example workbook that replicates that error, please?

Originally Posted by walruseggman

Can you post your workbook, or an example workbook that replicates that error, please?

I have uploaded my workbook and now it is silent here

It can be downloaded with the link below.

I have stripped most data and replaced names, the original sheet has almost 1 million rows. so it is important that I can automate as much as possible.

I need to remove duplicate rows for column O.

It would be nice to enable/disable the hidden rows, otherwise I am not able to see the original amount of rows anymore.


http://www.filedropper.com/test_30