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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,037
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,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

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