Forum Discussion

aldias21's avatar
aldias21
Copper Contributor
May 02, 2020

transpose some group of cells

I've been surfing on the internet but I can't find the exact answer to my problem. I want to transpose "some group of cells" that you can look at my screenshot below. Is that any fastest way to do that? Thank you.

excel
Formulas and Functions

2 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    May 02, 2020

    aldias21 

    Please find the Macro-Enabled Excel Workbook with a button called "Transpose Data" on Sheet1.

    You may click this button to run the macro which will transpose the numbers entered in column A in the desired format.

     

    The code underneath the button is as below...

     

    Sub TransposeData()
Dim i   As Long
Dim j   As Long
Dim lr  As Long
Dim x   As Variant

Application.ScreenUpdating = False

'Finding the last row with number in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row

'Clearing the destination range
Range("C:E").Clear

ReDim x(1 To (lr / 3) + 2, 1 To 3)

For i = 1 To lr Step 3
    j = j + 1
    x(j, 1) = Cells(i, 1)
    x(j, 2) = Cells(i + 1, 1)
    x(j, 3) = Cells(i + 2, 1)
Next i

Range("C1").Resize(j, 3).Value = x

Application.ScreenUpdating = True
End Sub

     

     

Resources