Hi,

Just wondering what may be the best method to get all cells in one
column into a sorted array.

If I sort the column, it affects the order of the whole sheet, which
isn't wanted.
The only alternative I can think of is a bubble sort of the array,
after asignment. Not very efficient.

Is there a better solution? I'm not too good with ranges, but can you
assign a range, sort it, then read that into an array ?

Thanks - Kirk

you can use a helper column off to the right. enter 1 to whatever down the
column, then sort on your column, add to array and resort on the helper
column to put it back in the original order.

--

Gary
Excel 2003


"kirkm" wrote in message ...
Hi,

Just wondering what may be the best method to get all cells in one
column into a sorted array.

If I sort the column, it affects the order of the whole sheet, which
isn't wanted.
The only alternative I can think of is a bubble sort of the array,
after asignment. Not very efficient.

Is there a better solution? I'm not too good with ranges, but can you
assign a range, sort it, then read that into an array ?

Thanks - Kirk

Hi,

And, you can load the array in one step

Dim myArray As Variant
myArray = [B1:B100]

Where B1:B100 contains the sorted data for the array

Regarding sorting in the spreadsheet verses bubble sort, it is my
understanding that its faster to do your sorts in the spreadsheet.

--
Thanks,
Shane Devenshire


"kirkm" wrote:

Hi,

Just wondering what may be the best method to get all cells in one
column into a sorted array.

If I sort the column, it affects the order of the whole sheet, which
isn't wanted.
The only alternative I can think of is a bubble sort of the array,
after asignment. Not very efficient.

Is there a better solution? I'm not too good with ranges, but can you
assign a range, sort it, then read that into an array ?

Thanks - Kirk