Removing Duplicates across multiple worksheets

**jd316632** · 11-16-2009, 11:25 AM

I'm taking an excel document given to me, running it through a series of queries in Access and exporting the results out via TransferSpreadSheet into Excel. The problem is some of the records meet the criteria of multiple queries. What I'm wanting to do is have an excel macro/vba go through and remove duplicates from the worksheets.

If possible I would like it to use Column Z for the unique identifer for each record and look through all of the sheets using Column Z from sheet 1, sheet 2, sheet 3, etc and compare them to each other. I found code on this forum but it's not working for me (is probably that I'm doing something wrong)

Option Explicit

Sub Delete_Duplicate_Rows()
Dim str1 As String, str2 As String
Dim r1 As Long, r2 As Long, c As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets

    With ws.UsedRange
    
        For r1 = .Rows.Count To 2 Step -1
            str1 = ""
            For c = 1 To .Columns.Count
                str1 = str1 & Cells(r1, c).Value
            Next c
                
            For r2 = r1 - 1 To 1 Step -1
                str2 = ""
                For c = 1 To .Columns.Count
                    str2 = str2 & Cells(r2, c).Value
                Next c
        
                If str1 = str2 Then
                    Rows(r1).Delete
                Exit For
                End If
            Next r2
        Next r1
    
    End With

Next ws

Application.ScreenUpdating = True
End Sub