Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Split a Long Row of Data Into Multiple Rows

We can use Excel Macros to organize our data. Below is one such example. Here what we going to do is split long row of data into multiple rows. Here the rule is we need 5 cells per row, then to start the next row. So data in F1 will move to A2, G1 to B2 and so on. Same on third row until data runs out.

Here is how our data currently in the sheet.

Below is the end result we need.

So here is the sample code to do that. You can customize the code to suit to your situation. New sheet will be added and result will be created in that sheet. At last sheet will renamed as "Result sheet" and activated. And you will get a confirmation message at end.

Dim WS As Worksheet
Dim WS_Result As Worksheet

Set WS = Worksheets("Input file")
Set WS_Result = Worksheets.Add

'find last column
Lastcol = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

Counter = 1
ResultRowCount = 1
For i = 1 To Lastcol
    If Counter = 6 Then
        Counter = 1
        ResultRowCount = ResultRowCount + 1
        WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i)
    End If
    WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i)

    Counter = Counter + 1
Next i

WS_Result.Name = "Result sheet"
MsgBox "Completed!", vbInformation, ""

Contact Form


Email *

Message *