Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA - Remove Duplicates Across Multiple Sheets in Workbook

Status
Not open for further replies.

Eitel13

Programmer
Feb 1, 2018
54
ZA
Also posted here:


I have multiple sheets in a particular workbook, and n each sheet there are Employee Numbers. The sheets have already been sorted in a way that Column A is always the Employee Number.

So what I need to do is loop through all the sheets and apply the RemoveDuplicates function to delete all duplicate Employee Numbers found in Column A.

Note - I am not trying to have the Employee Number appear on only one sheet; I am trying to have the Employee Number appear only once on each sheet.

This works below:

Code:
Sub deleteDuplicate()

    Dim ws As Worksheet
    Dim wkbk1 As Workbook
    Dim w As Long

    Set wkbk1 = Workbooks("3rd Party.xlsm")

    wkbk1.Activate

    With wkbk1

        For w = 1 To .Worksheets.count

            With Worksheets(w)

                .Range("A:M").RemoveDuplicates Columns:=1, Header:=xlYes

            End With

        Next w

    End With

End Sub

But when I change this line:

Code:
.Range("A:M").RemoveDuplicates Columns:=1, Header:=xlYes

to this:

Code:
.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes

I get this error:

Application-defined or object-defined error
 
Hi,

When you get that error message, hit DEBUG (with your macro still active) and determine what sheet you are on and if, in fact, you have data in the UsedRange on that sheet.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Hi Skip,

Thank you for getting back to me..

I actually had forgotten I posted this question, sorry..

I did manage to get a working solution:


Code:
Public Sub DeleteDuplicates2()
    Dim wb As Workbook, ws As Worksheet

    On Error Resume Next    'Expected error: wb not found
    Set wb = ThisWorkbook   'Workbooks("workbook.xlsm")

    If Not wb Is Nothing Then
        Application.ScreenUpdating = False
        For Each ws In wb.Worksheets
            ws.UsedRange.RemoveDuplicates Columns:=Array(1), Header:=xlYes
        Next
        Application.ScreenUpdating = True
    End If
End Sub

Thank you again :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top