Need everything in A column

[sksanjeev786]

Hi Team,

I have data from row1 to 100 from A to AQ column and I need everyting in Column A

A1 to A100 and then B1 to B100 data I need in A101 to following colurmn till AQ

1000​	1100​	1400​	1500​
1001​	1101​	1401​	1501​
1002​	1102​	1402​	1502​
1003​	1103​	1403​	1503​
1004​	1104​	1404​	1504​
1005​	1105​	1405​	1505​
1006​	1106​	1406​	1506​
1007​	1107​	1407​	1507​
1008​	1108​	1408​	1508​
1009​	1109​	1409​	1509​
1010​	1110​	1410​	1510​
1011​	1111​	1411​	1511​
1012​	1112​	1412​	1512​
1013​	1113​	1413​	1513​
1014​	1114​	1414​	1514​
1015​	1115​	1415​	1515​
1016​	1116​	1416​	1516​
1017​	1117​	1417​	1517​

 

[Georgiboy]

Maybe something like the below:

Sub test2()
    Dim var As Variant
    Dim x As Long, y As Long, z As Long
    
    var = Range("A1").CurrentRegion.Value
    ReDim oVar((UBound(var)) * UBound(var, 2))
    
    For y = 1 To UBound(var, 2)
        For x = 1 To UBound(var)
            oVar(z) = var(x, y): z = z + 1
        Next x
    Next y
    
    Range("A1").CurrentRegion.ClearContents
    Range("A1").Resize(UBound(oVar)) = Application.Transpose(oVar)
End Sub

 

[mohadin]

Hi
How about

Sub test()
    Dim a
    Dim i&
    a = Sheet1.Cells(1).CurrentRegion
    For i = 1 To UBound(a, 2)
        Sheet2.Cells((i - 1) * UBound(a) + 1, 1).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), i)
    Next i
End Sub

 

[Fluff]

Or with a formula

=LET(a,Sheet1!A1:AQ100,r,ROWS(a),s,SEQUENCE(r*COLUMNS(a),,0),INDEX(a,MOD(s,r)+1,INT(s/r)+1))

 

[sksanjeev786]

Hi
How about

Sub test()
    Dim a
    Dim i&
    a = Sheet1.Cells(1).CurrentRegion
    For i = 1 To UBound(a, 2)
        Sheet2.Cells((i - 1) * UBound(a) + 1, 1).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), i)
    Next i
End Sub

Hi Mohadin,

Thanks for the help
and just wanted to let you know I am getting an error while running with macro, but i am able to run the macro which is provided by Georgiboy

 

[sksanjeev786]

Maybe something like the below:

Sub test2()
    Dim var As Variant
    Dim x As Long, y As Long, z As Long
   
    var = Range("A1").CurrentRegion.Value
    ReDim oVar((UBound(var)) * UBound(var, 2))
   
    For y = 1 To UBound(var, 2)
        For x = 1 To UBound(var)
            oVar(z) = var(x, y): z = z + 1
        Next x
    Next y
   
    Range("A1").CurrentRegion.ClearContents
    Range("A1").Resize(UBound(oVar)) = Application.Transpose(oVar)
End Sub

thanks you Geogiboy it works for me

 

[sksanjeev786]

Or with a formula

=LET(a,Sheet1!A1:AQ100,r,ROWS(a),s,SEQUENCE(r*COLUMNS(a),,0),INDEX(a,MOD(s,r)+1,INT(s/r)+1))

Wowww, it is also works perfectlyyy

Thank you Fluff

 

[mohadin]

The code will copy the result in sheet2 so..
You can run this code below the sheet with data activated

Sub test()
    Dim a: Dim i&
    With ActiveSheet.Cells(1).CurrentRegion
    a = .Value
    .ClearContents
    For i = 1 To UBound(a, 2)
        .Cells((i - 1) * UBound(a) + 1, 1).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), i)
    Next i
    End With
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sksanjeev786]

The code will copy the result in sheet2 so..

Hi Mohadin,

I have tried but still not able to run getting error mentioned in the below Screen shot