clear text data in duplciate cells

[knockingmyhead]

Column A has text. I want to clean it up and have the first occurance of a name in that column and if the next cell down that has the same name clear that cell.


john 100 john 100

aaron 200 aaron 200
aaron 200 200
aaron 200 200

doe 350 doe 350
doe 350 350


if it matters there are two blank rows between names. I don't want to delete the row (as i have seen in many posts, just clear the cell)

thanks

 

[lrobbo314]

How about this?

Sub ClearDupes()
On Error Resume Next
Dim x As Long
x = Range("A" & Rows.Count).End(xlUp).Row()
    For x = x To 1 Step -1
        If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
            Cells(x, 1).Value = vbNullString
        End If
    Next x
    
End Sub

 

[knockingmyhead]

that didn't work, maybe spaces at the end affecting?

how about one to match the first 10 characters?

thanks for your help

How about this?

Sub ClearDupes()
On Error Resume Next
Dim x As Long
x = Range("A" & Rows.Count).End(xlUp).Row()
    For x = x To 1 Step -1
        If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
            Cells(x, 1).Value = vbNullString
        End If
    Next x
    
End Sub

 

[knockingmyhead]

thats ok I did a different way

Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

but it is erasing my header row even though "ss#" is in there?

 

[knockingmyhead]

Ok did another different way that Mirabeau ehlped with

Application.ScreenUpdating = True
[c:c].AutoFilter Field:=1, Criteria1:="="
[c2:c3000].SpecialCells(xlVisible).EntireRow.Delete
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

 

[mirabeau]

thats ok I did a different way

Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

but it is erasing my header row even though "ss#" is in there?

from your above, did you try?

Range("B2:B" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Edit. But on your post#5, that wasn't me.