Sep 23 2021 05:30 AM
SolutionOption 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
Sep 23 2021 06:25 AM
'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
Sep 23 2021 06:58 AM
Sep 23 2021 07:00 AM
Sep 23 2021 07:05 AM
Sep 23 2021 07:37 AM
@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
Sep 23 2021 05:30 AM
SolutionOption 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