-1

I am attempting to transpose values into new rows on the same sheet. Can someone help me with the proper vba code to be the output I am looking for?

Given: 

Col1    Col2  Col3  Col4
Title1  A     B     C
Title2  D     E     
Title3  F
title4  G     H

Wanted:

Col1    Col2  Col3  Col4
Title1  A          
Title1  B   
Title1  C
Title2  D     
Title2  E
Title3  F
Title4  G
Title4  H
Community
  • 1
  • 1
Hmg
  • 21
  • 1
  • 5
  • 1
    Please clarify the current layout and the desired one. Using fixed-width text might help. – ExactaBox Jan 26 '15 at 18:49
  • You might want to check out http://stackoverflow.com/questions/13174916/transpose-a-range-in-vba and http://stackoverflow.com/questions/24456328/creating-and-transposing-array-in-vba – Codo Jan 26 '15 at 18:56

1 Answers1

0

This quick macro should make short work of the process.

Sub transpose_in_place()
    Dim rw As Long, cl As Long
    With ActiveSheet
        For rw = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
            For cl = .Cells(rw, Columns.Count).End(xlToLeft).Column To 3 Step -1
                If Not IsEmpty(.Cells(rw, cl)) Then
                    .Rows(rw + 1).Insert
                    .Cells(rw + 1, 1) = .Cells(rw, 1).Value2
                    .Cells(rw + 1, 2) = .Cells(rw, cl).Value2
                    .Cells(rw, cl).Clear
                End If
            Next cl
        Next rw
    End With
End Sub

As the name suggests, it will perform the transposition in place so perhaps you should test it on a copy of your actual data before committing to the full process.

       enter image description here