• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Macro to transpose rows into Columns

I have a spreadsheet with 4 rows that need to be merged into 1 row. On the example spreadsheet attached (in sheet 1), rows 11 to 14 need to be merged into 1 row by adding 3 extra columns between columns D & E, and pasting the contents of cells d12, d13 & d14 into those extra columns. The same needs to happen with rows 15 to 18 and any subsequent groups of 4 rows underneath. The data always starts from row 11.

Sheet 2 in the axample shows the required results.
Excel-Test.xlsx
0
Ed_Snowden
Asked:
Ed_Snowden
1 Solution
 
SteveCommented:
The following macro code should do the job:

Sub Macro1()

    Columns("E:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D10:G10").Value = Array("Address 1", "Address 2", "Address 3", "Address 4")
    For x = 11 To Range("A65000").End(xlUp).Row
        If Cells(x, 1).MergeCells Then
        
            Cells(x, "E").Value = Cells(x + 1, "D").Value
            Cells(x, "F").Value = Cells(x + 2, "D").Value
            Cells(x, "G").Value = Cells(x + 3, "D").Value
    
            Rows(x + 1 & ":" & x + 3).Delete Shift:=xlUp
        
        End If
    Next x
End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

You can do it with formulas

Regards
Copy-of-Excel-Test.xlsx
0
 
Ed_SnowdenAuthor Commented:
Great. Works perfectly. Thanks very much
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now