Transpose 2 dimensional to 1 dimensional (single column)

tran780

New Member
Joined
Apr 17, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm writing a code that transpose 2D to 1D but it refuse to work. Please help to modify my code that make it work

Example:

Sheet1, data is in a range A2:E6

needs to be copied (transposed) onto

Sheet1, in a range D10:D34

> First row of the array (Sheet1!A2:E2) goes into Sheet1!D10:D14
> Second row (Sheet1!A3:E3) goes into Sheet2!D15:D19
> ... and so forth

Many thanks!
Sub two_dimensional_array()
Dim table(1 To 5, 1 To 5) As Variant
Dim i As Integer, r As Integer
For i = 1 To 5
For r = 1 To 5
table(i, r) = Cells(i + 1, r)
Next r
Next i
Range("d10:d34").Value = Excel.Application.Transpose(table)

End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I wouldn't use transpose:
VBA Code:
Sub OutputTableValuesToOneColumn()
    Dim arr() As String, cell As Range, i As Long
    Application.ScreenUpdating = False
    For Each cell In Range(Range("A2"), Range("E6")).Cells
        ReDim Preserve arr(i)
        arr(i) = cell.Value
        i = i + 1
    Next cell
    For i = LBound(arr) To UBound(arr)
        Cells(10 + i, "D") = arr(i)
    Next i
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
No Array required. Try this code.
VBA Code:
Sub Macro1()
Sheets("Sheet1").Activate
Dim cel As Range
Dim X As Long


For Each cel In Range("A2:E6")
Range("D10").Offset(X, 0) = cel.Value
X = X + 1
Next cel


End Sub
 
Upvote 0
Here is one more macro for you to consider...
VBA Code:
Sub TwoD2OneD()
  Dim R As Long, C As Long, X As Long
  Dim V As Variant, Data As Variant, Result As Variant
  Data = Sheets("Sheet1").Range("C2:N225")
  ReDim Result(1 To UBound(Data, 1) * UBound(Data, 2), 1 To 1)
  For R = 1 To UBound(Data, 1)
    For C = 1 To UBound(Data, 2)
      X = X + 1
      Result(X, 1) = Data(R, C)
    Next
  Next
  Sheets("Sheet2").Range("D2").Resize(UBound(Result)) = Result
End Sub
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Transpose 2 dimensional to 1 dimensional (single column)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Thank all, however please help me to modify my code. Why it doesn't work? I write something wrong, do I
 
Upvote 0
Thank all, however please help me to modify my code. Why it doesn't work? I write something wrong, do I
It’s just that you can’t use Transpose that way because it’s used to swap rows and columns, not to reduce a multi-dimensional array into a one-dimensional one.
 
Upvote 0
Your code is corrected below
VBA Code:
Sub two_dimensional_array()
Dim table(1 To 25) As Variant
Dim i As Integer, r As Integer
For i = 1 To 5
For r = 1 To 5
table((i - 1) * 5 + r) = Cells(i + 1, r)
Next r
Next i
Range("d10:d34").Value = Excel.Application.Transpose(table)
End Sub
Problem.
Table was declared 2D array 5columns 5 rows
D10:D34 is 25 rows with sinlge column. Excel cannot put values.
For D10:D34 there should be 1D array with 25 elements. In this code Table is 1D array with 25 elements.
I hope it is clear.
Any more clarification welcome.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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