clear contents if cells are equal except first

[ss6857]

I am try to format a report so it is simpler to read. Right now I export information from Access. The product names are repetitive and I want to use them as titles, so I want to keep the first title then delete all the similar ones underneath it. Right now my data looks similar to this when exported:

Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges
Kiwi
Kiwi
Kiwi
Kiwi
Kiwi
Grape
Grape

My goal is for it to look like this:

Apples
 
 
 
Oranges
 
 
Kiwi
 
 
 
 
Grape

But with my current code I'm getting this:

Apples
 
Apples
 
Oranges
 
Oranges
Kiwi
 
Kiwi
 
Kiwi
Grape

It skips every other one unless the title changes. I'm so close! Here is my code:

Do Until Cells(myRows, 1) = ""
    If Cells(myRows, 1) = Cells(myRows - 1, 1) Then
        Cells(myRows, 1).ClearContents
        myRows = myRows + 1
    End If
    myRows = myRows + 1
Loop

Thanks!

 

[villy]

Is it possible to just advance filter unique values?

 

[mikerickson]

If your fruit is in column A, put conditional formatting on A2 with the formula
=(COUNTIF($A$1:$A2,A2)>1) to turn the font color white. This can be copied to the rest of column A.

 

[ss6857]

I don't think so. Because when I advance filter > unique values only what it does is get all of the rows underneath it, which I need because it's contains information about the product. Unless I forgot how to use Advance filter correctly.

 

[mikerickson]

If you want the cells truly cleared instead of just disguised, you could work from the bottom up.

Dim rowNum as Long

With Range("A:A")
    For i = .Cells(.Rows.Count, 1).End(xlup).Row To 2 Step -1
        If .Cells(i, 1) = .Cells(i-1, 1) Then .Cells(i,1).ClearContents
    Next I
End With

 

[villy]

Try:

Sub del()
For i = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
    With WorksheetFunction
        If .CountIf(Range("B:B"), Range("B" & i)) > 1 Then
            Range("B" & i).ClearContents
        End If
    End With
Next
End Sub

 

[ss6857]

Wow! Thanks everybody for the replies! I used mikerickson's buttom up approach and it worked wonderfully! Thanks again!