CONCATENATEIF on a sorted range

[merce333]

I wrote the following function to concatenate a range of cells. It works fine, but I don't understand -- if a range is 1 row & multiple columns it will go to the final "Else" and will be transposed two times. I arrived at this via trial and error and was pleasantly surprised it worked, but still don't understand why it is necessary to transpose twice. Can anyone please shed any light? Thanks!

Public Function CONCATENATERANGE(ByVal rng As Range, Optional sDelimiter As String) As String
Dim Arr                 As Variant


    If rng.Cells.Count = 1 Then
        ReDim Arr(1 To 1, 1 To 1)
        Arr(1, 1) = rng.Value2
    ElseIf rng.Rows.Count > 1 Then
        Arr = rng.Value2
    Else
        Arr = Application.Transpose(Application.Index(rng.Value2, 1, 0))
    End If
    Arr = Application.Transpose(Arr)
    CONCATENATERANGE = Join(Arr, sDelimiter)
    
End Function

 

[pgc01]

Hi

I wrote the following function to concatenate a range of cells.

I guess this is just for the case of a vector range, meaning a range with only 1 column or only 1 row?

... l don't understand why it is necessary to transpose twice.

You don't have to transpose twice. You just have have to transpose if it's a range with multiple rows.

This means that you should only transpose in the first Else not in the second. Also delete the Transpose() at the end, after the If.

If fact, what you were doing is not wrong, just does not make sense. You were transposing the array twice, which means you get the original array again.

 

[pgc01]

Also, I believe this is equivalent to your code (not tested):

Public Function CONCATENATERANGE1(ByVal rng As Range, Optional sDelimiter As String) As String
Dim arr                 As Variant


    If rng.Rows.Count > 1 Then
        arr = Application.Transpose(rng.Value2)
    Else
        arr = Application.Index(rng.Value2, 0)
    End If
    CONCATENATERANGE1 = Join(arr, sDelimiter)
    
End Function

 

[merce333]

Hi PGC. Much respect for what you do, I've learned alot from your posts. Btw, I am using Excel 2010, Windows 7 64bit.
The code you posted is what I originally had, but it gives #VALUE! errors for all ranges that are 1 row and multiple columns, which is why I modified the code to what I posted. In arriving at the code I posted, here are some things I noticed in my testing but did not fully understand:
1. The single-cell case in the first "IF" clause was giving an error. I found (as I do so often) that Chip Pearson had the solution here http://www.cpearson.com/excel/ArraysAndRanges.aspx yet, he did not explain why it is necessary.
2. Here's where things really got strange, in my opinion. When a single row, multiple column array was passed to the function, it always took in a multidimensional array! Literally, if I looped through each cell that was passed in the single-row range, the Debug.Print was outputting cell values from the rows *below* the row that I passed! How?? If I passed a 1 row, 3 column range, VBA registered a 3x3 array. If i passed a 1 row, 4 column range, VBA took that as a 4x4 array, etc. I did some researching and saw "mountainclimber" replied with an explanation to Siddharth's answer here: https://stackoverflow.com/questions/11504024/fastest-way-to-read-a-column-of-numbers-into-an-array Yet, there was no explanation as to why. And as mountainclimber noted, the array still needs to be referenced as a multidimensional array Arr(i,j) which then gave me problems passing it to vba's JOIN function, which requires a "vector" array to be passed. (As i type this response, I just realized I could've used the INDEX function to pass just the vector portion of that multi-dimensional array to the JOIN function... but I digress...)

After about an hour of trial and error, I ended up doing the double transpose, which then worked fine and fast... but it still bugs me that I don't understand why it works, and the 2 items above -- particularly item #2 -- were especially vexing.
Any explanation would be greatly appreciated! Thanks again!!

 

[merce333]

Here is what I now have. It is more symmetrical, but still would love to know why VBA receives a "square" multi-dimensional array when passed a single row or single column range.

Public Function CONCATENATERANGE(ByVal rng As Range, Optional sDelimiter As String) As String
Dim arr                 As Variant


    If rng.Cells.Count = 1 Then
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rng.Value2
    ElseIf rng.Rows.Count > 1 Then
        arr = Application.Index(rng.Value2, 0, 1)
    Else
        arr = Application.Index(rng.Value2, 1, 0)
    End If
    arr = Application.Transpose(arr)
    CONCATENATERANGE = Join(arr, sDelimiter)
    
End Function

 

[pgc01]

Hi

The code you posted is what I originally had, but it gives #VALUE ! errors for all ranges that are 1 row and multiple columns, which is why I modified the code to what I posted.

I just tested and it worked OK for me. I tested with office 365, I can test again tomorrow at work with excel 2010 but I don't think it will be any different.

1. The single-cell case in the first "IF" clause was giving an error. I found (as I do so often) that Chip Pearson had the solution here http://www.cpearson.com/excel/ArraysAndRanges.aspx yet, he did not explain why it is necessary.

Just remember that Join() expects an array in the first parameter. If it's a single cell make sure you write it in an array.

2. Here's where things really got strange, in my opinion. When a single row, multiple column array was taken into the function, it always took in a multidimensional array! Literally, if I looped through each cell that was passed in the single-row range, the Debug.Print was outputting cell values from the row *below* the row that I passed! How?? I did some researching and saw "mountainclimber" replied with an explanation to Siddharth's answer here: https://stackoverflow.com/questions/11504024/fastest-way-to-read-a-column-of-numbers-into-an-array Yet, there was no explanation as to why. And as mountainclimber noted, the array still needs to be referenced as a multidimensional array Arr(i,j) which then gave me problems passing it to vba's JOIN function. (As i type this response, I just realized I could've used the INDEX function to pass just the portion of that multi-dimensional array to the JOIN function... but I digress...)

Not sure I understand well but

1 - Join() expects a 1D array

2 - You can read directly a continuous rectangular range into an array. In that case you'll always get a 2D array (the range has 2 coordinates, row and column)

arr = Range("A1:D1").Value ' result array(1 to 1, 1 to 4)
arr = Range("A1:A4").Value ' result array(1 to 4, 1 to 1)
arr = Range("A1:B3").Value ' result array(1 to 3, 1 to 2)

In the case of a vertical vector you can read it directly into a 1D array using the worksheet function transpose()
In the case of a horizontal vector you can read it directly into a 1D array using the worksheet function Index()

arr = Application.Transpose(Range("A1:A4").Value) ' result array(1 to 4)
arr = Application.Index(Range("A1:D1").Value, 0) ' result array(1 to 4)

Since they return a 1D array you can use them directly in Join(), like:

MsgBox Join(Application.Index(Range("A1:D1").Value, 0), ", ")

HTH

 

[Rick Rothstein]

Also, I believe this is equivalent to your code (not tested):

Public Function CONCATENATERANGE1(ByVal rng As Range, Optional sDelimiter As String) As String
Dim arr                 As Variant


    If rng.Rows.Count > 1 Then
        arr = Application.Transpose(rng.Value2)
    Else
        arr = Application.Index(rng.Value2, 0)
    End If
    CONCATENATERANGE1 = Join(arr, sDelimiter)
    
End Function

You do not need to vector through the arr variable, you can do the assignment directly. Also, I set up my Index function differently.

[table="width: 500"]
[tr]
	[td]Function CONCATENATERANGE1(Rng As Range, Optional sDelimiter As String) As String
  If Rng.Columns.Count = 1 Then
    CONCATENATERANGE1 = Join(Application.Transpose(Rng.Value2), sDelimiter)
  Else
    CONCATENATERANGE1 = Join(Application.Index(Rng.Value2, 1, 0), sDelimiter)
  End If
End Function[/td]
[/tr]
[/table]

 

[pgc01]

Here is what I now have. It is more symmetrical, but still would love to know why VBA receives a "square" multi-dimensional array when passed a single row or single column range.

Single row range is: 1 row x n columns
Single column range is: n rows x 1 column

They are both rectangles. Rectangle with one of the sides equal to 1 but still rectangles.

 

[merce333]

Thank you both for your time and valuable input. Here is what I ended up with. Note 2 things:
1. I still had to handle the single-cell case, as that was giving a #VALUE ! error with Rick's posted code. I can't say I understand why, but I'll take Chip Pearson's words at face value and just consider it a "special case".
2. With the code below, if I pass a single-row, multiple column (1x4) vector range to the "Rng" function input, and then in the function I put: Debug.Print Rng(4,4).Value I see in the immediate window the contents of the cell offset 3 rows below the range I passed. Why do I see that? I would expect something like a run-time '9' error, "Subscript out of range". So, why does the vector Rng passed to the function have access to cells below the range passed to it? I get the exact same result in the Immediate window if I instead type: Debug.Print Rng(1,4).Offset(3,0).Value It seems Offset becomes not very useful if you can achieve the same results without it. It also seems illogical that I can refer to Rng(4,4).Value when Rng is clearly only 1 row. At the end of the day, everything is working as intended, but I would really love to know why excel shows some of this behavior.

Function CONCATENATERANGE1(Rng As Range, Optional sDelimiter As String) As String
    If Rng.Cells.Count = 1 Then
       CONCATENATERANGE1 = Rng.Value2
    ElseIf Rng.Columns.Count = 1 Then
        CONCATENATERANGE1 = Join(Application.Transpose(Rng.Value2), sDelimiter)
    Else
        CONCATENATERANGE1 = Join(Application.Index(Rng.Value2, 1, 0), sDelimiter)
    End If
End Function