VBA to add a sequence of Row numbers and to transpose the Column numbers into those rows

[jcantin01]

I'm trying to create a row of numbers from an existing table and transpose the associated column numbers into the new sequence.
There are over 300 rows in the existing table to convert. Thanks.

Existing Table:
1000 100 100 50 75 55
1001 0 20 30 40 0
1002 30 40 0 60 100

New Table:
1000 100
1000 100
1000 50
1000 75
1000 55
1001 0
1001 20
1001 30
1001 40
1001 0
1002 30
1002 40
1002 0
1002 60
1002 100

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub jcantin()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 2)
   
   For r = 1 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(r, c) = "" Then Exit For
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 1)
         Nary(nr, 2) = Ary(r, c)
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 2).Value = Nary
End Sub

 

[jcantin01]

WORKED!! Thank you, Fluff

 

[Fluff]

You're welcome & thanks for the feedback.