Worked like a charm - thank you!

"Bob Phillips" wrote:

Lois,

Try this macro

Sub Reformat()
Dim i As Long
Dim cLastRow As Long

Application.ScreenUpdating = False
With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
.Cells(i + 1, "A").Resize(4, 1).EntireRow.Insert
.Cells(i + 1, "A").Value = .Cells(i, "B").Value
.Cells(i + 2, "A").Value = .Cells(i, "C").Value
.Cells(i + 3, "A").Value = .Cells(i, "D").Value
.Cells(i, "B").Resize(1, 3).ClearContents
Next i
End With
Application.ScreenUpdating = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lois Lane" <Lois wrote in message
...
Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is
in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to convert rows to
columns, but it didn't work.

Thanks, in advance!