Is there any way to separate every five row in a different column in excel?

[Adil96]

I want to separate every four row into column.

Like in screenshot, first 5 row that I bold of A becomes column of C D E F & G I want every 5 row of A becomes column of C D E F & G in new row

How can I do this?

In simple words I want to transpose but with condtions.

 

[Gary's Student]

See:

Is there any way to separate every four row in a different column in excel? - Super User

 

[Trebor76]

Hi Adil96,

Welcome to MrExcel!!

Here's a macro solution:

Option Explicit
Sub Macro1()

    Dim lngMyRow As Long
    Dim lngLastRow As Long
    Dim lngPasteRow As Long
    
    Application.ScreenUpdating = False

    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lngMyRow = 1 To lngLastRow Step 5
        lngPasteRow = lngPasteRow + 1
        Range("A" & lngMyRow & ":A" & lngMyRow + 4).Copy
        Range("C" & lngPasteRow).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Next lngMyRow
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[hiker95]

Adil96,

Welcome to the MrExcel forum.

Here is another macro solution for you to consider that does not use copy paste, and, should be faster.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 07/13/2016, ME952934
Dim r As Long, lr As Long, nlr As Long, nr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
nlr = Application.Ceiling(lr, 5)
nr = 1
For r = 1 To nlr Step 5
  Cells(nr, 3).Resize(, 5).Value = Application.Transpose(Range(Cells(r, 1), Cells(r + 4, 1)).Value)
  nr = nr + 1
Next r
Columns("C:G").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.