Creating a VBA to find and delete duplicate values within Excel workbook

[Nikolova84]

Hello,

I am searching a way to find and replace/delete duplicates of the particular list of data (lets say, positioned in column A, Sheet 1) in the entire Excel workbook.
What i mean is, if this is my data (in column A, bellow), is there a way to find all the duplicate values in Sheet 2, 3 et cetera and delete them, based on this list ?

Thank you in advance for you time and cooperation!

 

[breynolds0431]

Hi there.

It's indicated in the first sentence that you'd want the data to be replaced/deleted. Can you clarify?

Assuming all data is to be deleted: Is the corresponding data to be deleted in the other sheets also in column A, or could it be in random columns? As for the deletion itself, would you want the cell where the data was found to be cleared, the cell deleted (in that case deleted and shifted up?), or would it be preferred that the entire row where that data is found to be deleted?

 

[Nikolova84]

Hi there.

It's indicated in the first sentence that you'd want the data to be replaced/deleted. Can you clarify?

Assuming all data is to be deleted: Is the corresponding data to be deleted in the other sheets also in column A, or could it be in random columns? As for the deletion itself, would you want the cell where the data was found to be cleared, the cell deleted (in that case deleted and shifted up?), or would it be preferred that the entire row where that data is found to be deleted?

Hello,

Thank you for your reply!

Concerning your first question, yes, i clarify that i want data to be deleted. Regarding the second question, the coresponding data to be deleted would not be only in column A, but rather random within the sheets. And about the third question, i would like the data in the cell to be just cleared and cell to remain blanc.

Thank you once again.

 

[breynolds0431]

Okay. Sounds like replacing the value with a blank would better suit your needs. Give the below a test in a normal module.

Sub ClearList()

Dim wb As Workbook: Set wb = ThisWorkbook
'Declares sheet with data list
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
'Finds last row in ws (Sheet1)
Dim lrow As Long: lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'Sets data list range in column A of Sheet1 going to last row
Dim dlist As Range: Set dlist = ws.Range("A2:A" & lrow)

'loops through dlist
Dim c As Range, wkst As Worksheet
For Each c In dlist.Cells
    If Not c.Value = vbNullString Then
        'then loops through all worksheets except ws (i.e.; Sheet1)
        For Each wkst In wb.Worksheets
            If Not wkst.Name = ws.Name Then
                'Will replace found dlist item with a blank
                Dim rng As Range: Set rng = wkst.UsedRange
                With rng
                    .Replace What:=c.Value, Replacement:="", LookAt:=xlWhole
                End With
            End If
        Next wkst
    End If
Next c

End Sub

 

[Nikolova84]

Okay. Sounds like replacing the value with a blank would better suit your needs. Give the below a test in a normal module.

Sub ClearList()

Dim wb As Workbook: Set wb = ThisWorkbook
'Declares sheet with data list
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
'Finds last row in ws (Sheet1)
Dim lrow As Long: lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'Sets data list range in column A of Sheet1 going to last row
Dim dlist As Range: Set dlist = ws.Range("A2:A" & lrow)

'loops through dlist
Dim c As Range, wkst As Worksheet
For Each c In dlist.Cells
    If Not c.Value = vbNullString Then
        'then loops through all worksheets except ws (i.e.; Sheet1)
        For Each wkst In wb.Worksheets
            If Not wkst.Name = ws.Name Then
                'Will replace found dlist item with a blank
                Dim rng As Range: Set rng = wkst.UsedRange
                With rng
                    .Replace What:=c.Value, Replacement:="", LookAt:=xlWhole
                End With
            End If
        Next wkst
    End If
Next c

End Sub

Thank you for your help, regarding my problem! I am going to try your code as soon as possible! Wish you a wonderful day!

 

[Nikolova84]

Hello,

I tried your code and it works great. Just another thing, is it possible data in the sheet A, from which i made a comparison, to be formated in different color, when the value in the cell is unique and there aren't any duplicates in other sheets? For example, if cell A14 is unique value and doesn't have duplicates, i want this cell to be formated and somehow distinguished.

Thanks in advance for your help!

 

[Nikolova84]

Hello,

I tried your code and it works great. Just another thing, is it possible data in the sheet A, from which i made a comparison, to be formated in different color, when the value in the cell is unique and there aren't any duplicates in other sheets? For example, if cell A14 is unique value and doesn't have duplicates, i want this cell to be formated and somehow distinguished.

Thanks in advance for your help!

Another option would be to just delete duplicate values in column A of Sheet 1 also.