I have more than 11000 rows data in column "C" Like

C1 name
C2 Address line 1
C3 Address line 2
C4 Address line 3
C5 Address line 4
C6 Email
C7 Tel. No.

than after every 7 rows there are 2 or 3 rows are blank and there may be
some time more than 7 rows data like upto 11000 rows

What i want is to Transpose them.

Like

name ,Address line 1,Address line 2,Address line 3,Address line 4,
Email,Tel. No.
name ,Address line 1,Address line 2,Address line 3,Address line 4,
Email,Tel. No.
name ,Address line 1,Address line 2,Address line 3,Address line 4,
Email,Tel. No.

it takes more time so i created macros from record macro but it not working
i don't no more above VBA.

Sub Macro1()
Range("C1:C7").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("E2").Select
Application.CutCopyMode = False
End Sub

Pls Help me.

thanks
tiya

Is there any stable periodicity or is it realy "sometimes 2 sometimes
3" and "sometimes 7 sometimes 8"?

Thanks for reply
It may change sometime 7 or 6 it is atlist 6.
any help

"Roman" wrote:

Is there any stable periodicity or is it realy "sometimes 2 sometimes
3" and "sometimes 7 sometimes 8"?

Here's a brute force solution, where I reference (SIndex, 1) change the 1 to
be the column number your data is in (C = 3). The output goes in column 1 in
a new sheet, so if you want to put it, starting in E initialize ColIndex to 5
instead of 1.

If you need to be in a row relative to the Name cell in the same sheet then
you'll need a little more work. I'll leave that to you to discover.

Bob

Option Explicit
Sub XposeAddress()
Dim SIndex As Long
Dim DIndex As Long
Dim ColIndex As Integer
Dim BlankFlag As Boolean

BlankFlag = False
ColIndex = 1
DIndex = 1
For SIndex = 1 To 65536 ' bottom of sheet
If Worksheets("Source").Cells(SIndex, 1).Value < "" Then
If BlankFlag Then
ColIndex = 1
DIndex = DIndex + 1
End If
Worksheets("Dest").Cells(DIndex, ColIndex).Value = _
Worksheets("Source").Cells(SIndex, 1).Value
ColIndex = ColIndex + 1
BlankFlag = False
Else
BlankFlag = True
End If
Next SIndex

End Sub

in d1 =c1
in e1 = c2
contine for f1,g1 etc

in d2 =if(c2="","",if(c1="",c2,"")
in e2 = if(d2<"",c3,"")
contine for f2,g2,h2,etc

copy d2 to l2 down all of the rows

use auto filter to display the none blank rows and copy, paste special
values onto a blank sheet and you will end up with the fromat you want.

HTH

Chris
"Tiya" wrote in message
...
I have more than 11000 rows data in column "C" Like

C1 name
C2 Address line 1
C3 Address line 2
C4 Address line 3
C5 Address line 4
C6 Email
C7 Tel. No.

than after every 7 rows there are 2 or 3 rows are blank and there may be
some time more than 7 rows data like upto 11000 rows

What i want is to Transpose them.

Like

name ,Address line 1,Address line 2,Address line 3,Address line 4,
Email,Tel. No.
name ,Address line 1,Address line 2,Address line 3,Address line 4,
Email,Tel. No.
name ,Address line 1,Address line 2,Address line 3,Address line 4,
Email,Tel. No.

it takes more time so i created macros from record macro but it not
working
i don't no more above VBA.

Sub Macro1()
Range("C1:C7").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("E2").Select
Application.CutCopyMode = False
End Sub

Pls Help me.

thanks
tiya