Deleting duplicates from array and sorting

[Ryanside]

Hi Everyone,

I've been scouting around a few forums now to try and find a solution to my problem but haven't had any luck so far. I have a general knowledge of VBA but nothing too advanced.

I basically have a single dimensional array containing row numbers which I need to sort and remove duplicates from.

Can anyone help/point me in the right direction?

Thanks

[Andrew-R]

Well as Excel has native functions to both remove duplicates and sort data you'd be mad to try to write specific code to do this.

What I'd do is:

1. Put some data (with duplicates) in a column.
2. Start recording a macro
3. Select your data range and remove duplicates, using the "Remove duplicates" function.
4. Sort the list, using the sort function.
5. Stop recording your macro
6. Look at code.

Then all you have to do is a simple loop to write your data out to an out-of-the-way location, point your recorded macro code at that location and then have another simple loop to read the data back in.

[Ryanside]

Magic, thanks for the tip, managed to do it. I think I was looking for the long way round for some reason!

[Andrew-R]

Glad my advice helped. FWIW, initially I thought this would be a fairly trivial problem to solve programmatically, because both the removal of duplicates and the sorting can be done in a nested loop (with an outer loop running from 1 to x-1, where x is the number of items in the array, and an inner loop running from the outerloop+1 to x).

However, trying to perform both actions at once quickly became a nightmare. It would have been easier if I could have swallowed my pride and reset any duplicate values to an arbitrarily large value, but that seemed too messy.