try this

assumed col A & Col b has data.

if you run the below macro, output will be from Col D.

Sub TEST_IT()
Dim r As Integer, z As Integer, r1 As Integer

z = 1
r1 = 1
r = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To r
If Cells(i, "b").Value = "" Then
z = 1
r1 = Cells(Rows.Count, "e").End(xlUp).Row + 1
ElseIf Cells(i, "B").Value < "" Then
z = z + 1
Cells(r1, z + 2) = Cells(i, "B").Value
End If
Next i

End Sub



On Oct 25, 6:20*pm, Sriram wrote:
i have a maximum of 48 rows

Thanks

"Sriram" wrote:
hi,

i have data in the below format in excel

A 1
B 2
C 3
D 4
(empty row)
(empty row)
A 5
B 6
C 7
D 8

and it runs into few hundred thousand rows, is there any way in which i
could transpose data in the format

A B C D
1 2 3 4
5 6 7 8
without having to paste special and then transpose record by record

Thanks