CODEWITHSUNDEEP
exceldelete-rowvba
Tarik
Tarik

Reputation: 77

Removing rows that contain duplicated values

Need help, I have this code below:

Dim LRAS, matchFoundIndex, iCntr As Long
LRAS = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For iCntr = 3 To LRAS
    If Cells(iCntr, 1) <> "" Then
    matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & LRAS), 0)
    If iCntr <> matchFoundIndex Then
        Cells(iCntr, 14) = "Duplicate"
        'at this point duplicate
    End If
End If

Next iCntr
'-----------
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim deleteRow As Long
Dim wrksht As Worksheet
Set wrksht = ActiveSheet

For deleteRow = wrksht.Range("N" & Rows.Count).End(xlUp).Row To 3 Step -1
If wrksht.Range("N" & deleteRow).Value = "Duplicate" Then
    Rows(deleteRow).EntireRow.Delete
End If

Next deleteRow
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

This code basicly search duplicate values then says that the values are duplicate. Next step is that the second duplicated item is deleted. I want to have this build into one event instead of two separated events. Is there a way to do this? Or is there another way to delete duplicated rows based on the value in the first column?

Upvotes: 0

Views: 135

Answers (1)

A.S.H
A.S.H

Reputation: 29352

I think all you need is one statement:

Cells.RemoveDuplicates 1

where 1 indicate the first column, A, where the duplicates should be checked and removed.

If the keys are spread over many columns, say columns 1 and 3, you can remove duplicates according to these columns like this:

 Cells.RemoveDuplicates Array(1, 3)

Upvotes: 2

Related Questions