VBA code to Split and Sort String Values

[mchilapur]

Dear Excel Experts
I have a VBA code to loop through a column of 10K rows and store unique values in it. These unique values are basically years like 2018,2019,2020,2021 etc..

When i store unique values, the list is pretty much random like 2022,2019,2021,2018 etc (its because the original data itself is not sorted as per year.)
Is there any way that i can sort the data in ascending order of years after pulling unique values. I am adding this list to data validation later.

Please Login or Register  to view this content.

[Rick Rothstein]

Here is a function that will take a range of cells and return the unique values in that sorted (this takes the place of the loop you showed part of above). So, if you wanted to assign the output to a variable named unique_string for the range of cells, say, C1:C10000, you would do this...

unique_string = Sorted(Range("C1:C10000"))

which, if the range C1:C10000 will never change, can be short-cutted to this...

unique_string = Sorted([C1:C10000])

Note: The Sorted function has an optional second argument allowing you to specify the delimiter to use which defaults to a comma if omitted.

Here is the function (put it in a General Module)...

Please Login or Register  to view this content.

[mchilapur]

Does its Job perfectly...Many Thanks for your code..!!