convert data from horizontal to vertical

[premsi]

Could someone please help me with vba coding for the below excel query

raw data

name	score	name	score	name	score
zen	32	men	22	ken	58
ran	45	den	23	shen	56
connor	66	junior	24	zenith	25

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

Seeking the refine data as below

name	score
zen	32
ran	45
connor	66
name	score
men	22
den	23
junior	24
name	score
ken	58
shen	56
zenith	25

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks in advance!

 

[MickG]

Try this:-
Results start "K1".

[COLOR="Navy"]Sub[/COLOR] MG08Apr59
[COLOR="Navy"]Dim[/COLOR] Ray, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 Ray = ActiveSheet.Cells(1).CurrentRegion
  ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 2)
[COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 2
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
        c = c + 1
        nray(c, 1) = Ray(n, Ac)
        nray(c, 2) = Ray(n, Ac + 1)
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
Range("K1").Resize(c, 2).Value = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[premsi]

Try this:-
Results start "K1".

[COLOR=Navy]Sub[/COLOR] MG08Apr59
[COLOR=Navy]Dim[/COLOR] Ray, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
 Ray = ActiveSheet.Cells(1).CurrentRegion
  ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 2)
[COLOR=Navy]For[/COLOR] Ac = 1 To UBound(Ray, 2) [COLOR=Navy]Step[/COLOR] 2
    [COLOR=Navy]For[/COLOR] n = 1 To UBound(Ray, 1)
        c = c + 1
        nray(c, 1) = Ray(n, Ac)
        nray(c, 2) = Ray(n, Ac + 1)
    [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]Next[/COLOR] Ac
Range("K1").Resize(c, 2).Value = nray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick[/Thank you so much Mick, you made my day!]

 

[premsi]

Thank yo so much Mick, you are awesome! Cheers!

 

[MickG]

You're welcome