VBA Transpose

CJ_22

New Member
Joined
Mar 22, 2016
Messages
19
I am converting a column that has cells which contain comma separated values.
1601589967905.png


I want to take these cells and create another column that has each of these comma separated values in their own row.
1601589998375.png


I have found a script that works but I am also trying to deconstruct for learning purposes.
VBA Code:
Public Sub transpose_multiple()
Dim xArr() As String
Dim xAddress As String
Dim Rg As Range
Dim Rg1 As Range

On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set Rg = Application.InputBox("please select the data range:", "Transpose Tool", xAddress, Type:=8)
If Rg Is Nothing Then Exit Sub
Set Rg = Application.Intersect(Rg, Rg.Parent.UsedRange)
If Rg Is Nothing Then Exit Sub
Set Rg1 = Application.InputBox("please select output cell:", "Transpose Tool", Type:=8)
If Rg1 Is Nothing Then Exit Sub
xArr = Split(Join(Application.transpose(Rg.Value), ","), ",")
Rg1.Resize(UBound(xArr) + 1) = Application.transpose(xArr)
Rg1.Parent.Activate
Rg1.Resize(UBound(xArr) + 1).Select

End Sub

The one line I am getting hung up on is:
VBA Code:
xArr = Split(Join(Application.transpose(Rg.Value), ","), ",")

I get what "Split" and "Join" do but I do not understand what Application.transpose(Rg.Value) is doing specifically. If anyone can help explain to me exactly what is doing I would be very grateful.

Thank you,
Chris
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
The questioned instruction do what the Excel "Transpose" native function does; ie it tranform a 1 row * N columns array (the result of vba "Split") to an N rows * 1 Column one.

For a test, add these lines to your macro:
VBA Code:
'...
xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")
Range("A10").Resize(5, 5).Value = xArr                           '<<< ADD
Range("G10").Resize(5, 5).Value = Application.Transpose(xArr)    '<<< ADD
Stop                                                            '<<< ADD
Rg1.Resize(UBound(xArr) + 1) = Application.Transpose(xArr)
'...
Run the macro; it will halt on that "Stop" added line; now check what is in the A10 block and what in the G10 block and will see the difference

Bye
 
Upvote 0
Let's dissect the 'Star of the Show' from inside out:

VBA Code:
Dim xArr As Variant
Dim Rg As Range
xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")

1. Rg.Value

You can get the values into an array as easy as:
VBA Code:
Dim Arr As Variant
Arr = Rg.Value
  • In your case, imagine the array looks like this:
    • String1
    • String2
    • String3
  • It's a column with three values, but it is not a one-dimensional (1D) array which Split or Join require.
  • It's a 2D (one-based) (one-column) array i.e. in your case it has three rows and one column referring to its first and second dimension respectively.
  • You access its elements with: Arr(1, 1), Arr(2, 1), Arr(3, 1).
2. Application.Transpose(Rg.Value)

The 'proper' way to continue would be to implement a new array and write the values to it:
VBA Code:
    Dim Arr2 As Variant, i As Long
    Redim Arr2 (1 To UBound(Arr, 1))
    For i = 1 To UBound(Arr, 1)
        Arr2(i) = Arr(i, 1)
    Next i

Now you can even do this;
VBA Code:
Arr = Arr2

and forget about Arr2.

That is exactly what in this case Application.Transpose does.

VBA Code:
Arr = Application.Transpose(Rg.Value)

It has its limitations of maximally 65535 elements per dimension and some problems with dates in which cases you will have to use the 'proper' way.

Now imagine the result in your case as a row:
String1, String2, String3

3. Join(Application.Transpose(Rg.Value), ",")


You have three strings containing comma separated strings. So let's visualize them like this:

s1, s2, s3, s4 | s5, s6, s7 | s8, s9

Join using the comma delimiter will create one 'huge' string:

s1, s2, s3, s4, s5, s6, s7, s8, s9

4. Split(Join(Application.Transpose(Rg.Value), ","), ",")


Now Split will write all the comma separated values (elements) in the 'huge' string to an array:

VBA Code:
xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")

This didn't even cover the basic material of the issue, but I would suggest you experiment yourself.
You can transpose any range or array or e.g. other data structures, and just create your own examples.
Here's one: You could prove that the limit is 65535 by trying to transpose an array with one more element than allowed. It will not crash your computer, it will just show an error message.
 
Upvote 0
Let's dissect the 'Star of the Show' from inside out:

VBA Code:
Dim xArr As Variant
Dim Rg As Range
xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")

1. Rg.Value

You can get the values into an array as easy as:
VBA Code:
Dim Arr As Variant
Arr = Rg.Value
  • In your case, imagine the array looks like this:
    • String1
    • String2
    • String3
  • It's a column with three values, but it is not a one-dimensional (1D) array which Split or Join require.
  • It's a 2D (one-based) (one-column) array i.e. in your case it has three rows and one column referring to its first and second dimension respectively.
  • You access its elements with: Arr(1, 1), Arr(2, 1), Arr(3, 1).
2. Application.Transpose(Rg.Value)

The 'proper' way to continue would be to implement a new array and write the values to it:
VBA Code:
    Dim Arr2 As Variant, i As Long
    Redim Arr2 (1 To UBound(Arr, 1))
    For i = 1 To UBound(Arr, 1)
        Arr2(i) = Arr(i, 1)
    Next i

Now you can even do this;
VBA Code:
Arr = Arr2

and forget about Arr2.

That is exactly what in this case Application.Transpose does.

VBA Code:
Arr = Application.Transpose(Rg.Value)

It has its limitations of maximally 65535 elements per dimension and some problems with dates in which cases you will have to use the 'proper' way.

Now imagine the result in your case as a row:
String1, String2, String3

3. Join(Application.Transpose(Rg.Value), ",")


You have three strings containing comma separated strings. So let's visualize them like this:

s1, s2, s3, s4 | s5, s6, s7 | s8, s9

Join using the comma delimiter will create one 'huge' string:

s1, s2, s3, s4, s5, s6, s7, s8, s9

4. Split(Join(Application.Transpose(Rg.Value), ","), ",")


Now Split will write all the comma separated values (elements) in the 'huge' string to an array:

VBA Code:
xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")

This didn't even cover the basic material of the issue, but I would suggest you experiment yourself.
You can transpose any range or array or e.g. other data structures, and just create your own examples.
Here's one: You could prove that the limit is 65535 by trying to transpose an array with one more element than allowed. It will not crash your computer, it will just show an error message.

I think I am following you. If you have some time could you submit your script in its entirety so I can try it?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top