Moving data from column to row in groups of 250 per row for fourrows, then skip a row and continue t

[Eric Hess]

I am looking for help with a formula in Excel that will pull all the
data from column A on one sheet and transpose the data to a new sheet
in rows. I would like to have the data end up with 4 rows of 250
cells accross, skip a row, then the next 4 rows of 250 cells.

 

[Gord Dibben]

Not a formula, but a macro.

Sub TransposeTest()
Dim c As Long
Dim rngData As Range
Dim lngRow As Long
Dim lngCol As Long
Dim NumRows As Integer
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set rngData = Range("A1", Cells(Rows.Count, 1).End(xlUp))
For c = 1 To rngData.Rows.Count Step 250
lngRow = lngRow + 1
For lngCol = 0 To 249
rngData(lngRow, 2 + lngCol).Value = rngData(c + lngCol, 1).Value
Next
Next c
For i = rngData(rngData.Count).Row To rngData(1).Row + 1 Step -4
Rows(i).EntireRow.Insert
Next i
Columns(1).Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP