Macro Help --- Transpose Row To Column

[joi3288]

Hi all,

I have this data in my spreadsheet:

item | image1 | image2 | image3 | image4 | image5
guitar-1 | k_g_1.png | b_g_1.jpg | e_g_1.jpg | e_g_2.jpg | e_g_3.jpg
guitar-2 | k_h_1.png | b_h_1.jpg | e_h_1.jpg | e_h_2.jpg | e_h_3.jpg

*I have more than a hundred rows on my spreadsheet here.

I want it to appear it this way.
guitar-1 | k_g_1.png
guitar-1 | b_g_1.jpg
guitar-1 | e_g_1.jpg
guitar-1 | e_g_2.jpg
guitar-1 | e_g_3.jpg
guitar-2 | k_h_1.png
guitar-2 | b_h_1.jpg
guitar-2 | e_h_1.jpg
guitar-2 | e_h_2.jpg
guitar-2 | e_h_3.jpg

You can place these columns maybe to Columns H and I.

Thanks.

 

[Krishnakumar]

Hi,

Sub kTest()
    Dim ka, k(), i As Long, j As Long, n As Long
    ka = [a1].CurrentRegion
    ReDim k(1 To UBound(ka, 1) * UBound(ka, 2), 1 To 2)
    
    For i = 2 To UBound(ka, 1)
        For j = 2 To UBound(ka, 2)
            n = n + 1
            k(n, 1) = ka(i, 1): k(n, 2) = ka(i, j)
        Next
    Next
    If n Then
        Columns("a:b").Insert
        [a1].Resize(, 2).Value = [{"Item","Image"}]
        [a2].Resize(n, 2).Value = k
    End If
End Sub

HTH

 

[GlennUK]

It takes less than a minute ( well, if you know it off by heart, anyway ), using this method:

http://www.tek-tips.com/faqs.cfm?fid=5287

 

[joi3288]

Thanks Kris.

 

[joi3288]

Thanks Glenn.