Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting a Concatenated Range
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 |
#2
|
|||
|
|||
Sorting a Concatenated Range
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 |
#3
|
|||
|
|||
Sorting a Concatenated Range
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting a range of cells that get value from other cells | Excel Discussion (Misc queries) | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel |