CODEWITHSUNDEEP
vbaexcel
AmandaNicole
AmandaNicole

Reputation: 33

Delete duplicate values within single worksheet, for multiple worksheets in workbook

I have a workbook with 102 worksheets. I want to cycle through each worksheet independently and remove duplicates within that worksheet (not across the workbook as a whole).

I currently have this code:

Sub DeleteDuplicates()
Set ws = ThisWorkbook.Worksheets

    For Each X In ws
        X.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    Next

End Sub

I believe it is deleting duplicates that appear anywhere in the workbook, not just within a single worksheet. Is there a way to limit this code so that it only removes duplicates from within the specific worksheet it is operating on?

Upvotes: 0

Views: 60

Answers (1)

tanstaafl1988
tanstaafl1988

Reputation: 31

This cycles through each worksheet in your workbook. You can specify worksheets if you need to skip any by adding IF statements.

Public Sub DeleteDuplicates()

    Dim ws as Worksheet
    Dim wb as Workbook

    Set wb = ThisWorkbook

    For Each ws In wb.Worksheets
        ws.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    Next ws

End Sub

Upvotes: 2

Related Questions