Moving data from column to row in groups of 250 per row for fourrows, then skip a row and continue the same throughout the data in column A
 

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.

Moving data from column to row in groups of 250 per row for four rows, then skip a row and continue the same throughout the data in column A
 

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

On Mon, 11 Jul 2011 11:25:19 -0700 (PDT), Eric Hess wrote:

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.