VBA transpose data rows to 2 columns

bunburyst

New Member
Joined
Apr 18, 2018
Messages
24
Hi, please, I need a vba code to transpose my data
from the following in sheet 1:

Data range is B3:IZ734

1.jpg


to the following in sheet 2:

2.jpg


A working code would be greatly appreciated,

Tks.

Remy
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi
Try
VBA Code:
Sub test()
    Dim a, b
    Dim i, x
    ReDim a(1 To 734 - 2 / 2)
    With Sheets("sheet1")
        For i = 1 To UBound(a)
            a(i) = .Cells(1 + i * 2, 2).Resize(2, 9)
        Next
        With Sheets("sheet2")
            For i = 1 To UBound(a)
                .Cells(3 + x, 2).Resize(UBound(a(i), 2), 2) = Application.Transpose(a(i))
                x = UBound(a(i), 2)
            Next
        End Sub
 
Last edited:
Upvote 0
OOOPs
Correction
VBA Code:
Sub test()
    Dim a, b
    Dim i, x
    ReDim a(1 To 734 - 2 / 2)
    With Sheets("sheet1")
        For i = 1 To UBound(a)
            a(i) = .Cells(1 + i * 2, 2).Resize(2, 9)
        Next
        With Sheets("sheet2")
            For i = 1 To UBound(a)
                .Cells(3 + x, 2).Resize(UBound(a(i), 2), 2) = Application.Transpose(a(i))
                x = UBound(a(i), 2)
            Next
        End With
    End With
End Sub
 
Upvote 0
@mohadin
As the data is going out to column IZ, don't you need to change the resize?
 
Upvote 0
Hey, thanks for your help.
The macro works but does not continue through the rest of the cells and rows. This is the result:
4.jpg


I remember you that i have data range b3:iz734
 
Upvote 0
Just change to
VBA Code:
            a(i) = .Cells(1 + i * 2, 2).Resize(2, 261)
so sorry for this
 
Upvote 0
Just change to
VBA Code:
            a(i) = .Cells(1 + i * 2, 2).Resize(2, 261)
so sorry for this
Thanks my friend but the macro does not continue with the rest of the rows. The macro stops when transposing the data in rows 3 and 4.
The macro should continue through rows 5 and 6, etc.

:(
 
Upvote 0
VBA Code:
Sub test()
    Dim a, b
    Dim i, x
    Dim lr, lc
    With Sheets("sheet1")
        lr = .Cells(Rows.Count, 3).End(xlUp).Row
        lc = .Cells(3, Columns.Count).End(xlToLeft).Column
        ReDim a(1 To (lr - 2) / 2)
        For i = 1 To UBound(a)
            a(i) = .Cells(1 + i * 2, 2).Resize(2, lc)
        Next
        With Sheets("sheet2")
            For i = 1 To UBound(a)
                .Cells(3 + x, 2).Resize(UBound(a(i), 2), 2) = Application.Transpose(a(i))
                x = UBound(a(i), 2)
            Next
        End With
    End With
End Sub
 
Upvote 0
Again
VBA Code:
Sub test()
    Dim a, b
    Dim i, x
    Dim lr, lc
    With Sheets("sheet1")
        lr = .Cells(Rows.Count, 3).End(xlUp).Row
        lc = .Cells(3, Columns.Count).End(xlToLeft).Column
        ReDim a(1 To (lr - 2) / 2)
        For i = 1 To UBound(a)
            a(i) = .Cells(1 + i * 2, 2).Resize(2, lc)
        Next
        With Sheets("sheet2")
            For i = 1 To UBound(a)
                .Cells(3 + x, 2).Resize(UBound(a(i), 2), 2) = Application.Transpose(a(i))
                x = UBound(a(i), 2) + x - 1
            Next
        End With
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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