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

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

--
Regards,
Tom Ogilvy



"Rashid Khan" wrote in message
...
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

Wow, You are a magician. It works like a magic wand.
Thanks a lot.
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Rashid Khan" wrote in message
...
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