Sorting a Concatenated Range

G

Greg

I have a sort problem I'd like some assistance with.

I have a function that is returning the following concatenation from a
column range. =ConcatenateRange("",G8:G498)

326 327 328 336 338 340 61 76 86 101 111 126 136 161 176 186 201 211
226 236 53 56 153 156 978 981 1117 1120 1121 1827 1828 1833 1834
15 20 26 36 1116 1117 1832 1833
8 2 6 6 2 3 9 0 313 301 302 326 327 328 336 338 340 15 20 26 36 61 76
86 101 111 126 136 161 176 186 201 211 226 236 53 56 153 156 978 981
326 327 328 336 338 340 15 20 26 36 61 76 86 101 111 126 136 161
176 186 201 211 226 236 53 56 153 156 978 981
326 327 328 336 338 340 15 20 26 36 61 76 86 101 111 126 136 161 176
186 201 211 226 236 53 56 153 156 978 981
etc etc

I'd like this data sorted - lowest to highest. I'm getting the
concatenation from the following fuction. I've tried a couple of
sorting fuctions from this group but not much luck. My VBA skills are
minimal.
Thanks in Advance.


Public Function ConcatenateRange(Separator As String, ParamArray
ConcatRange() As Variant)
Dim vItem As Variant, rngCell As Range
Dim vRetVal As Variant

For Each vItem In ConcatRange

For Each rngCell In vItem.Cells

vRetVal = vRetVal & rngCell.Value & Separator

Next rngCell

Next vItem

ConcatenateRange = Left$(vRetVal, Len(vRetVal) - Len(Separator))

End Function
 
G

Greg

Hi Toppers,

I can't realy sort the cells in the range I think, they form part of a
matrix. I think sorting them will stuff up the matrix.
 
M

Mike Fogleman

You will still need to sort the range before it is concatenated. If you need
to leave the dataset intact, then make a copy of the range, sort the copy,
concatenate the sorted copy and then delete the sorted copy.

Mike F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top