Using dynamic Unique Function

[hishamkhatri]

Hello Everyone,

This is my first post here and I could really use your help!

I'm working on a Macro right now. There is a list of values out of which I would be extracting unique values using =UNIQUE formula. Now, this formula needs to be dynamic because the list of values would be copy pasted by a user and it cannot be turned into Table everytime or else it'll be an extra work.

So, what I did is, I used the unique formula and referenced the entire Column but that results into having an extra value. "0 (Zero)" for all the blank cells. Is there a way, I can remove the 0 and only have cells that has value unified?

I used the same logic in GSheet and it didn't give me any trouble. My organization has recently migrated to M365 so, I need to convert my existing GSheet Macro into Excel Macro!

Could really use your help!

Thank you!
Hisham Khatri

 

[Peter_SSs]

Welcome to the MrExcel board!

I would not use a whole column reference unless you really have something like 1,000,000 rows of data. Just choose a range that will be big enough to cover any expected data.

So try something like

=UNIQUE(FILTER(A1:A10000,A1:A10000<>""))

 

[hishamkhatri]

Welcome to the MrExcel board!

I would not use a whole column reference unless you really have something like 1,000,000 rows of data. Just choose a range that will be big enough to cover any expected data.

So try something like

=UNIQUE(FILTER(A1:A10000,A1:A10000<>""))

Thank you so much, Peter!!! It worked like a charm!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.