Reduce amount of dimensions in an array

Question

I have Range(one dimensional), that I want to summarize in one cell by concatenating all values. I thought that I could do just:

Dim Data_array()
Dim Source_Range as Range

Set Source_Range = Selection
Data_array() = Source_Range.Value2
Source_range.Offset( -1 ,0).Value = Join(Data_array, ", ")

This however returns error 5 because Data_array is a ( 1 To X, 1 To 1) array it has two dimensions, and Join on last line requires that you provide just one dimenstional array.

So my question would be is there a way to remove that "1 To 1" dimension?
If not how would you concatenate a one dimensional range in one cell.

Example

Desired Result

     A
1    2, 4, 6
2    2
3    4
4    6

Well you need to define X, which I thought you had, since you used it in your example. `X = Source_Range.Count Redim Data_array(1 to x)` — Kyle, Mar 02 '15 at 16:33
possible duplicate of [how to merge all column into one cell in excel?](http://stackoverflow.com/questions/8135995/how-to-merge-all-column-into-one-cell-in-excel) — Patrick McDonald, Mar 02 '15 at 16:40

score 2 · Accepted Answer · answered Mar 02 '15 at 18:07

You were so close! The code below assumes you will select the cells below the empty target cell. I't is simply two tweaks from your original code:

Sub testing()

    Dim Data_array()
    Dim Source_Range As Range

    Set Source_Range = Selection
    Data_array() = WorksheetFunction.Transpose(Source_Range.Value2)
    Source_Range.Offset(-1, 0).Resize(1, 1).Value = Join(Data_array, ", ")

End Sub

Gary's Student · Answer 2 · 2015-03-02T16:47:03.920

1

For your data, I would not bother with a VBA array. Consider:

Public Function Konkat(rin As Range) As String
    For Each r In rin
        v = r.Value
        If v <> "" Then
            Konkat = Konkat & "," & v
        End If
    Next r
    Konkat = Right(Konkat, Len(Konkat) - 1)
End Function

enter image description here

This is because in your code, data_Array is actually a two-dimensional array.

edited Mar 02 '15 at 16:47

answered Mar 02 '15 at 16:30

Gary's Student

95,722
10
59
99

score 1 · Answer 3 · answered Mar 02 '15 at 16:35

Below is an idea. NOTE: I'm not sure that the OFFSET part of your code does what you want it to do. Test the code, and let me know if so.

Dim Data_array()
Dim Source_Range As Range
Dim nIncrement As Integer

Set Source_Range = Selection
nIncrement = 1

ReDim Data_array(1 To Source_Range.Rows.Count)
For Each cel In Source_Range
    Data_array(nIncrement) = cel.Value
    nIncrement = nIncrement + 1
Next cel

Source_Range.Offset(-1, 0).Value = Join(Data_array, ", ")

score 1 · Answer 4 · answered Mar 02 '15 at 17:03

You can use the INDEX worksheet function to slice out a column or row.

Sub JoinRangeComma()

    Dim vaData As Variant
    Dim rSource As Range
    Dim wf As WorksheetFunction

    Set wf = Application.WorksheetFunction
    Set rSource = Selection
    vaData = rSource.Value2
    rSource.Cells(1).Offset(-1, 0).Value = Join(wf.Index(wf.Transpose(vaData), 1, 0), ", ")

End Sub

Reduce amount of dimensions in an array

4 Answers4