Announcement

Collapse
No announcement yet.

Transposing one row and multiple columns into several rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Transposing one row and multiple columns into several rows



    Hi there,

    I'm trying to change a big table of data that looks like this:
    City Name Last name Colour of shirt Length of pants Number of socks
    Stockholm Jurgen Karlsson Red 130 2
    into something that looks like this:

    City Name Last name Question Value
    Stockholm Jurgen Karlsson Colour of shirt Red
    Stockholm Jurgen Karlsson Length of pants 130
    Stockholm Jurgen Karlsson Number of socks 2
    But I really can't make it work and have no idea where to start when doing it with VBA (I'm a rookie)

    Anyone who can point me in the right direction?

    Thanks!

  • #2
    Try this. It puts the results on Sheet2 so you may need to amend sheet names.
    Sub x()

    Dim r As Long

    With Sheets("Sheet1")
    .Range("A1:C1").Copy Sheets("Sheet2").Range("A1")
    Sheets("Sheet2").Range("D1:E1").Value = Array("Question", "Value")
    For r = 2 To .Range("A" & Rows.Count).End(xlUp).Row
    .Cells(r, 1).Resize(, 3).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).Resize(3)
    Union(.Cells(1, 4).Resize(, 3), .Cells(r, 4).Resize(, 3)).Copy
    Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True
    Next r
    End With

    End Sub

    Comment


    • #3


      Originally posted by StephenR View Post
      Try this. It puts the results on Sheet2 so you may need to amend sheet names.
      Exactly what I was looking for! Thank you SO much!!

      Comment

      Working...
      X