Formula in VBA -- formula is too long

[macrorookie]

Evening -
I closed an earlier post where I don't believe I explained myself clearly, and I believe my post was in the wrong section. Apologies for the confusion.

I'm having a heck of a time finding a simple solution to my problem.

Problem:
In the attached sample workbook, starting with cell E2 I would like to show a list of the unique values shown in columns A,B,C and D. I plan to record this formula into a VBA/macro, and while the current array formula that I'm using works outside of VBA, it is too long for VBA (e.g., VBA has an array character limit of 255).

Any clever solutions?

Thanks for the help!

[jindon]

Do you want to stick with formula?
Else

Please Login or Register  to view this content.

Edit: found a typo was a(i,1)
And the data range should be changed as fixed above.

[GC Excel]

Hello macrorookie,

I was working on a solution similar to the one posted by Jindon...
Posting it anyway..

Please Login or Register  to view this content.

[YasserKhalil]

I know that the solutions provided is excellent and perfect
Here's my try splitting the array formula to two parts to avoid the error

Please Login or Register  to view this content.

[YasserKhalil]

@Mr. Jindon
I think the code need to be revised as the count of unique items in Column A:D is 45 not 44 ... so Columns("a:b") to be a:d
And the whole number of items is 244 while is your 238 ... so

Please Login or Register  to view this content.

would be

Please Login or Register  to view this content.

[AB33]

YasserKhalil,
That was my initial thought of splitting the code in to parts, but Jerry came up with a great alternative of using "Named Range"

Link

http://www.excelforum.com/excel-prog...y-formula.html

[jindon]

Yeah, I missed that

Please Login or Register  to view this content.

[macrorookie]

Outstanding solution, gang. Thanks so much for the help. All of the solutions worked exactly as I need to them to work, and I learned a few things through your various ideas.
Much appreciated!!