CODEWITHSUNDEEP
excelvbaduplicates
Chris MacDonald
Chris MacDonald

Reputation: 13

Looping through sheets removing duplicates

My code seems to be working. I want to check for improvements, potential bugs and unintended consequences.

I have spreadsheets with duplicate information, some have 100 sheets inside each file.

I don't want to go through each sheet manually using remove duplicate information.

After searching I think I have a solution.

Sub RemoveDuplicates()
 ' RemoveDuplicates Macro
 ' Selects all values then removes duplicates
 '

     ' Declare Current as a worksheet object variable.
         Dim Current As Worksheet
         Dim starting_ws As Worksheet
         Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning

         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets
            Current.Activate
            
            LastRow = Cells(Rows.Count, "A").End(xlUp).Row
            ActiveSheet.Range("A1" & LastRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11), Header:=xlYes
      
         Next
         
    starting_ws.Activate 'activate the worksheet that was originally active
    
End Sub

I am only looking for duplicates on each sheet, not comparing worksheets. Also the number of columns will stay the same.

Upvotes: 1

Views: 295

Answers (1)

VBasic2008
VBasic2008

Reputation: 54883

Remove Duplicates (Multiple Columns and Worksheets)

  • Use Option Explicit.
  • This ActiveSheet.Range("A1" & LastRow) is surely wrong. Think about it.
  • You have to qualify Range, Cells, Rows and Columns e.g. Current.Cells, Current.Rows.Count...
  • You don't have to activate Current. Current is the ActiveSheet in your case (since you have activated it), so no need to use ActiveSheet instead.
  • Do not let Excel to define the range: you define it.

A Code With a Question

Option Explicit

Sub removeDupes()
     
     ' Here's a question:
     '     Why can't I use 'Cols' instead of the array written explicitly?
     Dim Cols As Variant
     Cols = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
     
     Const FirstAddress As String = "A1"
     Dim wb As Workbook
     Set wb = ActiveWorkbook
     
     Dim ws As Worksheet
     Dim LastCell As Range
     
     For Each ws In wb.Worksheets
         Set LastCell = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(, 10)
         ws.Range(FirstAddress, LastCell).RemoveDuplicates _
             Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11), Header:=xlYes
         ' This doesn't work.
         'ws.Range(FirstAddress, LastCell).RemoveDuplicates _
             Columns:=Cols, Header:=xlYes

         ' Try this with ByVal array.
         'ws.Range(FirstAddress, LastCell).RemoveDuplicates _
             Columns:=(Cols), Header:=xlYes
     Next

End Sub

Upvotes: 1

Related Questions