Option Explicit

Sub TransposeDataSAS()
Application.ScreenUpdating = False
Dim dr As Long
Dim i As Long

dr = 2
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 3
Cells(i, 1).Resize(3).Copy
Cells(dr, 2).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
dr = dr + 1
Next i
Range("b2").Select
Columns(2).Resize(, 3).AutoFit
Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...


If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tony Pass" wrote in message
...
I often get lists of contacts that are generated in one long column. They
are
often may be 4 or 5 rows per record. In order to be able to use the data
I
need to transpose the rows in each record (one column) to new columns. It
may
include Company Name in Row A1 and contact name in Row B1 and perhaps
Phone
or Web Site in C1 etc. The records are always the same amount of rows. I
have
tried copy, transpose, and text to rows and anything else I can think of.
Can
anyone make a suggestion ?? Thanks in advance Tony Pass