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

Greg,
Sort G8:G498 BEFORE calling the concatenation function.

"Greg" wrote:

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

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.

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
"Greg" wrote in message
oups.com...
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.

Can you convert those formulas to values, then sort them?

Greg wrote:

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

--

Dave Peterson