• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove Duplicates Base 0 vs. Base 1

polarisking

Member
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
Code:
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
Code:
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".
 

Attachments

  • DeDupe Test.xlsm
    16.2 KB · Views: 11
Back
Top