i need a macro to transpose a number(example-5cells of a column[example at the 1st column]) and do it that every 4 columns?

i need a macro to transpose a number Variable X (example-4cells of a column) ,
Variable Y  [example at the 1st column] and do it that every Variable Z=X+N(rows) {example every 4-rows}
i am sending  an excel
the right solution must be a analytic ,also i need general variables,and some comments for the macro or the formula.
Who is Participating?
Saurabh Singh TeotiaCommented:
You can use something like this do what you are looking for...

Sub arrangedata()

    Dim i As Long
    Dim lrow As Long, lr As Long

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    lr = 1
    For i = 2 To lrow Step 4

        Range("A" & i & ":A" & i + 3).Copy
        Range("F" & lr).PasteSpecial xlValues, Transpose:=True
        lr = lr + 1
        Application.CutCopyMode = False

    Next i

End Sub

Open in new window

Rob HensonFinance AnalystCommented:
Looking back at my formula driven option in prior question, this can also be done with formula, albeit quite lengthy.

See attached. Values from columns E to J can then be filtered for non-blank and then copied elsewhere.

As for notes to explain, the formula uses a combination of various text functions to identify the separator at various positions and separates the text values based on that separator and the cumulative length of previous values.

Rob H
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.