clear text data in duplciate cells

knockingmyhead

New Member
Joined
Jul 14, 2012
Messages
46
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows
How about this?

Code:
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

New Member
Joined
Jul 14, 2012
Messages
46
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?

Code:
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

New Member
Joined
Jul 14, 2012
Messages
46
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

New Member
Joined
Jul 14, 2012
Messages
46
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

Banned user
Joined
Nov 4, 2010
Messages
2,075
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?
Code:
Range("B2:B" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete


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

Watch MrExcel Video

Forum statistics

Threads
1,127,114
Messages
5,622,811
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top