Try this small macro:
Sub Xpose()
Dim r As Range, i As Long
Dim r2 As Range
Set r = Range("A1:A4")
r.Select
Set r2 = Range("B1")
For i = 1 To Rows.Count
If r(1).Value = "" Then Exit Sub
r.Copy
r2.PasteSpecial Transpose:=True
Set r = r.Offset(6, 0)
Set r2 = r2.Offset(1, 0)
Next
End Sub
It will put the transpose in column B, leaving the original data alone. For
example:
1 1 2 3 4
2 7 8 9 10
3 13 14 15 16
4 19 20 21 22
25 26 27 28
31 32 33 34
7 37 38 39 40
8
9
10
13
14
15
16
19
20
21
22
25
26
27
28
31
32
33
34
37
38
39
40
Macros are very easy to install and use:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window
If you save the workbook, the macro will be saved with it.
To remove the macro:
1. bring up the VBE window as above
2. clear the code out
3. close the VBE window
To use the macro from Excel:
1. ALT-F8
2. Select the macro
3. Touch RUN
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
--
Gary''s Student - gsnu200908
"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