 SOLVED

# Clear repeating values in column A, keep rows intact

abc
abc
abc
bcd
bcd
cde
cde
cde
To appear as
abc

bcd

cde

6 Replies
best response confirmed by Ranju0714 (New Contributor)
Solution

# Re: Clear repeating values in column A, keep rows intact

Option 1: using formulas.

Let's say the data are in A1 and below.

Enter =A1 in B1.

Enter =IF(A2=A1,"",A2) in B2.

Fill down from B2.

If you wish, you can copy column B and paste it as values over column A. You can then remove the formulas in column B.

Option 2: using a macro.

``````Sub ClearDuplicates()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Range("A" & Rows.Count).End(xlUp).Row
For r = m To 2 Step -1
If Range("A" & r).Value = Range("A" & r - 1).Value Then
Range("A" & r).ClearContents
End If
Next r
Application.ScreenUpdating = True
End Sub``````

# Re: Clear repeating values in column A, keep rows intact

``````'VBA code
Sub ClearWithoutShiftRows()
rws = Cells(Rows.Count, "A").End(xlUp).Row
For j = rws To 2 Step -1
If Cells(j, 1).Value = Cells(j - 1, 1).Value Then Cells(j, 1).Value = ""
Next
End Sub``````

# Re: Clear repeating values in column A, keep rows intact

Thanks much, formula worked

# Re: Clear repeating values in column A, keep rows intact

Not know VBA - where does this code go

# Re: Clear repeating values in column A, keep rows intact

ALT+F11 keys combination Then paste the code into the module

After you run pressing F5

# Re: Clear repeating values in column A, keep rows intact

@Ranju0714 I see you have an answer, here is a second way to do it.

=IF(SEQUENCE(COUNTA(B3:B10))=XMATCH(B3:B10;B3:B10);INDEX(B3:B10;SEQUENCE(COUNTA(B3:B10)));"")

Example attached. I am starting to love sequence.

- Geir