List up all cells values related to one raw as new raw

Question

I need your support to re-arrange an excel sheet, listing all values in columns to be in new raws.

As is:

enter image description here

To be: enter image description here

Answer 1

One of the ways is you use loops of repetition to perform the transposition of the data

Public Sub transpose()
    Dim lastRow As Integer
    Dim startRow As Integer
    Dim lastCol As Integer
    Dim resultRow As Integer
    
    
    resultRow = 2
    lastRow = Planilha1.Range("A1000").End(xlUp).Row
    lastCol = Planilha1.Range("A1").End(xlToRight).Column
    startRow = 1
    
    For iRow = startRow To lastRow
        
       For iCol = 2 To lastCol
        'col 7 and 8 = key and value
        Planilha1.Cells(resultRow, 7) = Planilha1.Cells(iRow, 1)
        Planilha1.Cells(resultRow, 8) = Planilha1.Cells(iRow, iCol)
        
        resultRow = resultRow + 1
       Next
    Next
    
End Sub