Excel VBA code to turn a row with a variable number of columns into fixed-length rows

Solution 1:

1. Go to the developer tab and click "record macro"
2. Then add a new sheet
3. Stop he recording
4. look at the code generated by clicking "Visual Basic" it is probably in "Module 1"

Repeat for next problem and use this code as a base for what you are trying to build.

When you get stuck, Google your problem. If that doesn't solve it, post the code you have here and specify exactly what you want it to do, what you think it does and what you think is wrong or don't understand.

Solution 2:

Code in VBA / Macro:

Public Sub distribute()
    Application.ScreenUpdating = False
    Dim wkb As Workbook
    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Set wkb = ThisWorkbook
    Set wks1 = wkb.Worksheets("Sheet1")
    Set wks2 = wkb.Worksheets("Sheet2")
    wks2.Rows.Clear
    therow = 1
    othersheetrow = 1
    morerows = True
    While morerows
        morecolumns = True
        thecolumn = 3
        abscolumn = thecolumn
        While morecolumns
            a = wks1.Cells(therow, thecolumn)
            If (a = "") And (thecolumn = abscolumn) Then
                morerows = False
                morecolumns = False
            ElseIf (a = "") And (thecolumn > abscolumn) Then
                morecolumns = False
                therow = therow + 1
            Else
                If (therow = 1) Then
                    For i = 1 To abscolumn
                        wks2.Cells(therow, i) = wks1.Cells(therow, i)
                    Next i
                    therow = therow + 1
                    thecolumn = thecolumn - 1
                Else
                    othersheetrow = othersheetrow + 1
                    For i = 1 To abscolumn - 1
                        wks2.Cells(othersheetrow, i) = wks1.Cells(therow, i)
                    Next i
                    wks2.Cells(othersheetrow, i) = wks1.Cells(therow, thecolumn)
                End If
            End If
            thecolumn = thecolumn + 1
        Wend
    Wend
    Application.ScreenUpdating = True
    themessage = MsgBox("Finished", vbInformation)
End Sub

Open VBA / Macro con ALT+ F11, then insert a new module under ThisWorkbook and paste the code on the right side.

There is a variable called thecolumn that it's initialized on 3, meaning that it is the first column to consider before creating a new entry (row) on Sheet2.