[RESOLVED] VB2008-How to use "Array" member of excel

[drmerby3737]

Hi all,

I am using Visual Basic 2008 to manipulate some data in a spreadsheet. I am having a problem using Excel's built in RemoveDuplicates function. Here is the line of code I need to fix...

wSheet.Range(strRange).RemoveDuplicates(Columns:=Array(1, 2, 3, 4, 5, 6), Header:=Excel.XlYesNoGuess.xlNo)

The keyword "Array" is where the issue lies. What is "Array" a member of? I tried the object library but did not find it in there. I can use another method to remove the duplicates but am still curious.

Thanks!

[Siddharth Rout]

What is the exact error message that you are getting?

Try this

Dim Columns As Object
Dim Header As XlYesNoGuess

Set their values and then try this?

wSheet.Range(strRange).RemoveDuplicates(Columns, Header)

[drmerby3737]

This does not work. Let me be more specific. I am essentially trying to convert a line of excel vba code to Visual Basic. The code works in Excel vba. Here is the original code with no modifications...

wSheet.Range(strRange).RemoveDuplicates(Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlNo)

Visual Basic does not recognize "Array" or "xlNo". So the error says "xlNo" is not declared and would say "array" is not declared if "array" was not a keyword in Visual Basic(it says "array" is a type and cannot be used as an expression"). I was able to fix the xlNo by using the Excel Library reference, Excel.xlYesNoGuess.xlNo. I got the syntax from the Excel Object Library. However, I need something like Excel.????.Array or something similar to tell Visual Basic the keyword "Array" is coming from the Excel Library.

Thanks!

[Siddharth Rout]

Try this

wSheet.Range(strRange).RemoveDuplicates( _
Columns:=New Object() {1, 2, 3, 4, 5, 6}, _
Header:=Excel.XlYesNoGuess.xlNo)

[drmerby3737]

Works great, thanks!