Transpose Columns to Rows

[Rashid Khan]

Hello All,
I am using Office XP and have the following problem.
The data is in the following format on Sheet1in Cols A, B and C and set of
Seven Rows.

A B C
1 Data1 Data2 <blank>
2 Data4 Data5 Data6
3 Data7 Data8 <blank>
4 Data9 Data10 <blank>
5 Data11 Data12 Data13
6 Data14 <blank> <blank>
7 Data16 <blank> <blank>

(Row 8 is Blank)

Similar format of Seven Rows from Row 9 to 15 as above and then a blank row
(Row 16) followed by set of Seven Rows from Rows 17 to 23.......... and so
on.

I wish to transpose the data from Sheet1 to Sheet2 (columns) in the
following manner.
A B C D E F G
H I J K
Data1 Data2 <blank> Data4 Data5 Data6 <blank> Data7 Data8 <blank>
Data9 ... till the last <blank>

and repeat for the following set of Seven Rows. The <blank> will never
appear in Col A but it can appear in Col B and/or Col C.

Can this be achieved thru VBA.. Any help or a better idea would be greatly
appreciated.

Thanks in advance

Rashid Khan

 

[Tom Ogilvy]

Sub TesterAA()
Dim cell As Range, rng1 As Range, rng As Range
Dim i As Long
Dim rw As Long
With Worksheets("Sheet1")
.Rows(1).Insert
Set rng = Columns(1).SpecialCells(xlBlanks)
End With
For Each cell In rng
rw = rw + 1
Set rng1 = cell.Offset(1, 0).Resize(7, 3)
For i = 1 To 21
Worksheets("Sheet2").Cells(rw, i).Value = _
rng1(i).Value
Next
Next
Worksheets("sheet1").Rows(1).Delete
End Sub

 

[Rashid Khan]

Wow, You are a magician. It works like a magic wand.
Thanks a lot.