Solved: Transpose column A to rows

[karlos]

Been modifing several dozen excel sheets this week, latest problem,

Column A down to A 5000 or so is populated with Formula.

I need to transpose column A data every 25 may be more but this can be changed I assume by manually changing a macro.

So move or copy all data in Column A in blocks of 25 to rows

so row 1 would have 25 (column data A1 to A25)

row 2 would have the next 25 (column data A26 to A50)

row 3 woulde have the next 25 Column data A51 to 75)

I could transpose manually but the task looks daunting,

Can anyone assist please.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow + (25 - LastRow Mod 25) To 24 Step -25

.Cells(i - 24, "A").Resize(25).Copy
.Cells(i - 24, "B").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=True
.Rows(i - 23).Resize(24).Delete
Next i
.Columns(1).Delete
End With

With Application

.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[karlos]

Many thanks - brilliant!

[elketa1252]

Hey, I have a question, how about if I want to have blocks of rows of 9, is there a way your VBA can be changed to it? I changed some of the numbers in the VBA but I can't get it to work.

Thanks in advance!!!.

[Bob Phillips]

Start a new thread and explain your situation there, maybe with an example workbook.