remove duplicates - vba

[lezawang]

Hi
I wrote the code below. It did not work until I typed Columns:=1
Why is that? why I need columns=1 when I said the range is a1:a25. Thank you so much.

Sub removeduplicate()
    Range("a1:a25").RemoveDuplicates Columns:=1
End Sub

 

[Michael M]

With a simple example that you have used, it would seem no to make sense to use columns, but if you can imagine for example that you want to remove duplicates that might be in 2 different columns !!

Sub removeduplicate()
    Range("a1:C25").RemoveDuplicates Columns:=Array(1, 2)
End Sub

would check to see if there were duplicates in either column (1) OR column (2)

 

[lrobbo314]

It's because that method has 2 arguments. The column to look at and whether or not the range has header values. The header argument is optional but the column argument is needed.

Your range has only 1 column, so it would make sense that you could leave the column argument out. But, a range could have multiple columns, and the method would need to know which column to focus on when removing duplicates.

 

[lezawang]

Thank you all. How can I know which argument is optional and which is not. I checked the object browser and it does not tell me which on is optional and which one is not. In excel if the argument is bold then it is must but here in Object browser is defined as this

Sub RemoveDuplicates([Columns], [Header As XlYesNoGuess = xlNo])

 

[Michael M]

I believe the only optional argument is the Yes / No for the headers
See here

https://docs.microsoft.com/en-us/office/vba/api/excel.range.removeduplicates