excel-VBA looping through sheets removing duplicates

VBasic2008 2020-11-28 23:01:11

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
     Next

End Sub

Chris MacDonald 2020-12-04 00:17:47

Thank you very much. Just looked up what Option Explicit is, very useful, it think I did see it in code samples i was referring too. I see what you mean about referencing a cell then a row. I kept chopping and changing with using current then activesheet, I understand my mistake there. And I do find it strange you have to use an explicit array for the columns. So I assume from your example that using ws.Rows.Count will count just the rows with data in it? Excellent reply thanks again,

VBasic2008 2020-12-04 02:21:19

Since Excel 2007, ws.Rows.Count (Rows.Count in a worksheet) means row 1048576, so it's actually the last cell in column A: cell A1048576. Only the End(xlUp) 'jumps' up to the last non-empty cell. It is equivalent to pressing CRTL+UP. And finally Offset(, 10) or Offset(0, 10) means ten columns to the right to determine the cell in the same row but in column K, so in the next line (ws.Range(FirstAddress, LastCell)...) we can define the complete range: from A1 to KwhateverRow.

Chris MacDonald 2020-12-04 04:17:06

Excellent thanks! That explains perfectly clearly what those are doing. Thank you for helping a complete beginner to vba like me. @VBasic2008

VBA looping through sheets removing duplicates

Remove Duplicates (Multiple Columns and Worksheets)

热门帖子

热门github