Excel VBA - Using RemoveDuplicates in a module with Option Base 1 declared.

There is likely a very simple answer to this question, but I have not found anything in the Excel Help files or in the more popular forums.

I have this piece of code that is part of a much larger routine. Running Excel 2010.

Code:
Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select Selection.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
This code works when it is included in a module with Option Base set to default (0). When I try to run it in a module with option base set to 1, I get the following error: "Run-time Error '5': Invalid Procedure Call or Argument"

I know it has something to do with the Columns:=Array(1,2) used to specify that I want it to look at Columns A and B when identifying the duplicates, but I cannot figure out how to change this statement to make it work with Option Base 1.

Thanks in advance for your help!
Gary
|
Answer
Answer
Take a look at Help for the Array function.  You want to use an argument of the form:

Columns:=VBA.Array(1, 2)

This will eliminate the effect of using option base 1 on the Array function, and your subroutine should work OK regardless of the Option Base setting.
Ron

13 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated July 16, 2021 Views 4,726 Applies to: