Remove Duplicates Base 0 vs. Base 1

[polarisking]

I'm having a problem getting the RemoveDuplicates Method to work when i use Option Base 1.

One way to employ the the RemoveDuplicates Method is to "feed" the Columns: option with (arrayname) where you've already loaded the array with a simple counter-based loop.

It appears from my testing, and some googling, that if you set the Option Base to 1, this process fails, where all other things being equal, it works just fine when Option Base is set to 0.

Base 0

Option Explicit
Option Base 0
Sub DeDupeCols()
 
  Dim rng  As Range
  Dim cols  As Variant
  Dim I  As Integer
 
 
  Set rng = Range("A1").CurrentRegion
 
  ReDim cols(0 To rng.Columns.Count - 1)
 
  For I = 0 To UBound(cols)
  cols(I) = I + 1
  Next I
 
  rng.RemoveDuplicates Columns:=(cols), Header:=xlYes
 
End Sub

Base 1

Sub DeDupeCols()
 
  Dim rng  As Range
  Dim cols  As Variant
  Dim I  As Integer
 
  Set rng = Range("A1").CurrentRegion
 
  ReDim cols(1 To rng.Columns.Count)
 
  For I = 1 To UBound(cols)
  cols(I) = I
  Next I
 
  rng.RemoveDuplicates Columns:=(cols), Header:=xlYes
 
End Sub

Sheet1 is used to observe the deduping while Sheet2 is used to reload the Sheet1 data between "tests".

 

[NARAYANK991]

Hi ,

Just to add to the documentation , so that it is all available in one place :

http://yoursumbuddy.com/get-unique-per-row-values-removeduplicates/

Narayan