remove duplicate lines, but not delete rows

[excelos]

hello

I would like to ask you if you know a way to delete duplicate lines and leave the first instance of them, but not deleting rows as well, as shown in this screenshot:

thanks!

 

[JoeMo]

Welcome to the board Excelos. Try this on a copy of your worksheet:

Sub RemoveSomeDupInfo()
Dim rng As Range, delRng As Range, dataRws As Long
Dim dStr As String, v1(1 To 3), v2(1 To 3), ctr As Long

Set rng = ActiveSheet.Range("a1").CurrentRegion
dataRws = rng.Rows.Count - 1
Set rng = rng.Offset(1, 0).Resize(dataRws)
Set delrws = Nothing
For Each rw In rng.Rows
    For i = 1 To 3
        v1(i) = rw.Cells(1, i)
        v2(i) = rw.Offset(1, 0).Cells(1, i)
        If v1(i) <> v2(i) Then
            Exit For
        Else
            ctr = ctr + 1
        End If
        If ctr = 3 Then
            If delrws Is Nothing Then
                Set delrws = rw.Offset(1, 0).Resize(, 3)
            Else
                Set delrws = Union(delrws, rw.Offset(1, 0).Resize(, 3))
            End If
        End If
    Next i
ctr = 0
Next rw
If delrws Is Nothing Then
    MsgBox "No duplicates found"
Else
    delrws.ClearContents
End If
End Sub

 

[excelos]

Welcome to the board Excelos. Try this on a copy of your worksheet:

Sub RemoveSomeDupInfo()
Dim rng As Range, delRng As Range, dataRws As Long
Dim dStr As String, v1(1 To 3), v2(1 To 3), ctr As Long

Set rng = ActiveSheet.Range("a1").CurrentRegion
dataRws = rng.Rows.Count - 1
Set rng = rng.Offset(1, 0).Resize(dataRws)
Set delrws = Nothing
For Each rw In rng.Rows
    For i = 1 To 3
        v1(i) = rw.Cells(1, i)
        v2(i) = rw.Offset(1, 0).Cells(1, i)
        If v1(i) <> v2(i) Then
            Exit For
        Else
            ctr = ctr + 1
        End If
        If ctr = 3 Then
            If delrws Is Nothing Then
                Set delrws = rw.Offset(1, 0).Resize(, 3)
            Else
                Set delrws = Union(delrws, rw.Offset(1, 0).Resize(, 3))
            End If
        End If
    Next i
ctr = 0
Next rw
If delrws Is Nothing Then
    MsgBox "No duplicates found"
Else
    delrws.ClearContents
End If
End Sub

it works brilliantly, thanks!

however, the above xls I posted is just an example, will I have any problem with other xls files? I mean that the duplicates are in columns b and c, etc

I was thinking that I should determine which columns to scan for duplicates

 

[JoeMo]

I assumed that the data are sorted so that duplicates are already grouped together and that you are using a tabular format with column headers like the example you posted. Also that the duplication of interest is in the first three columns of the table. Finally, that the worksheet you want to apply the code to is the active worksheet when you run the code.

If your table layout changes, say for example the table starts in cell D3 instead of A1, then you need to change "a1" in the first set statement to "D3". If you want to look for duplicates in columns other than the first three columns of the table, then you would have to make some changes to the code to ensure that those columns are being checked for duplicate entries.