Remove Duplicates Column Array Value From Cell

[yoshi_5]

Hello,

I've looked everywhere and I can't find an answer to this, so I appreciate any and all help on solving this issue.

I have an Excel workbook that will open other workbooks and remove duplicates. However, the columns that need to be included in the remove duplicates will vary depending on the workbook being open.

In the main workbook I have the column array values stored in a cell and identified for each of the different workbooks that it will open, however, what I can't do is pass the cell value of the column array to the visual basic code.

For instance in my main workbook, cell A1 = 1, 5 and I'm trying to populate the value from cell A1 into this code and run in the newly opened workbook:

Please Login or Register  to view this content.

In another instance in my main workbook, cell A2 = 1, 3, 5, 7 and I'd then like to pass that value into the code and run in the newly opened workbook:

Please Login or Register  to view this content.

I just can't figure out how to turn that string or value into what the Columns:=Array value is when trying to remove duplicates.

Any help on getting the value that's in A1 or A2 into the Columns:=Array(___) code?

I hope I've been able to explain myself clearly. Thanks in advance for any assistance!

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

The Split function returns an array of the separated values from the text string using the comma as the delimiter.

[yoshi_5]

Hi AlphaFrog,

Thank you for the code, however I get an error.

Run-time error '458':

Variable uses an Automation type not supported in Visual Basic

Unsure where to look next...

[AlphaFrog]

Hi AlphaFrog,

Thank you for the code, however I get an error.

Run-time error '458':

Variable uses an Automation type not supported in Visual Basic

Unsure where to look next...

Can you show your code. Not sure what "Variable" the error refers to. What line of code is highlighted when you click the Debug button on the error dialog?

Try this to convert the text-array to a numeric-array.

Please Login or Register  to view this content.

[yoshi_5]

Hello AlphaFrog,

I'm not able to show the code. However, the code runs smoothly if the columns array value is manually entered as:

Please Login or Register  to view this content.

The yellow highlighted line when debug error is pressed on the RemoveDuplicates line of code.

I've now also tried your second suggestion and that is also providing me an error. Run-time error '5' Invalid procedure call or argument

When debugging that error, the same line of code is highlighted in yellow. However, when I hover over the MyArray2(i) and MyArray(i) pieces of code it shows they both equal "Subscript out of range".

Any other ideas..?

[AlphaFrog]

Hello AlphaFrog,

I'm not able to show the code. However, the code runs smoothly if the columns array value is manually entered as:

Please Login or Register  to view this content.

The yellow highlighted line when debug error is pressed on the RemoveDuplicates line of code.

I've now also tried your second suggestion and that is also providing me an error. Run-time error '5' Invalid procedure call or argument.

When debugging that error, the same line of code is highlighted in yellow. However, when I hover over the MyArray2(i) and MyArray(i) pieces of code it shows they both equal "Subscript out of range".

Any other ideas..?

If MyArray has no values then whatever you are doing to read the values from the main workbook is not correct. Or the cell value is not comma delimited.

[yoshi_5]

Hi AlphaFrog,

Thank you for your patience,

When I step through the code, the MyArray values do actually show up, as it loops through the MyArray value is first "1" and then on the next loop "5", but when I step past the "Next i", that is when both go Subscript out of range...

Ugh...

[AlphaFrog]

...but when I step past the "Next i", that is when both go Subscript out of range...

That's because once past the Next i line, the i value is one greater than the array size. That's normal and wouldn't cause the RemoveDuplicates line to error.

Are the values of MyArray2 correct as seen in the Locals window

Are you using MyArray2 as the array in RemoveDuplicates?

[jindon]

yoshi_5

Try this whay

Please Login or Register  to view this content.

[yoshi_5]

Hi jindon -

Your code worked perfectly! I'd ask what it all means but I'm only just dangerous in Visual Basic and not a pro by any means, so I'd probably not understand anything you'd say.

AlphaFrog - Thanks so much for your help as well. It's so nice to have so much information available and willing participants to share their knowledge.

Cheers!

[jindon]

This is all my guess, not really sure why.

RemoveDuplicates method
1) only accepts 0 based 1D variant/Numeric type array.
2) parenthesis needs to be added when variable used for column protocol.

So, e.g. if A1 contains 1,3, evaluate method returns 1d 1 based array x(1)=1 and x(2) = 3
Then shift the index from 1 to 0 by Redim Preserve key word, makes x(0)=1 and x(1) = 3

And be careful for using Array function.
If you declare Option Base 1 in general declaration area(I have never done it though), Array function returns 1 based 1d array which RemoveDuplicates method will not accept.
So better use VBA.Array function which always returns 0 based array regadless of Option base declaration.

HTH.

[AlphaFrog]

You're welcome yoshi and well done jindon. I thought it had to do with the array format, but I probably wouldn't have figured it out. I'm using Excel 2003 and can't test RemoveDuplicates.