I am trying to automate the following step. From a single row of Excel data, I would like to copy and paste each cell value into separate rows, but staggered by columns, as shown in the picture. Is there a VBA macro code that is able to perform this step? Thanks in advance!

screenshot of intended output

1 Answer 1


There is a VBA function called Offset. As you loop through the cells in row 1, increment a variable and use that to govern the Offset for each value.

Option Explicit

Sub staggerRows()

    Dim i As Long, rng As Range

    With Worksheets("sheet1")

        For Each rng In .Range(.Cells(1, "B"), .Cells(1, .Columns.Count).End(xlToLeft))

            i = i + 1
            rng.Offset(i, 0) = rng.Value2

        Next rng

    End With

End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .