List Rows to Columns

[Ricky]

Hi,

I have an extensive contact list is column A, with each contact
separated by 2 empty rows.

Each contact has a varying amount of info such as address, phone
numbers etc, so some contacts occupy 5 lines, some 7 etc in columns A.

My problem - how do I re-arrange all these contacts in col A so that
one contact is displayed in one row. This will then have all my 300
contacts being displayed in rows A1 down to A300.

Any help with a macro to achieve this would be greatly appreciated.

Regards, Ricky

 

[Tim Williams]

What do you want to do with each contact so it fits one one line:
split it over multiple columns, or combine all the lines into one cell ?

Tim

 

[Patrick Molloy]

Option Explicit
Sub Combine()
Dim rw As Long
Dim cl As Long
For rw = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(rw - 1, 1) <> "" Then
For cl = 1 To Cells(rw, Columns.Count).End(xlToLeft).Column
Cells(rw - 1, Columns.Count).End(xlToLeft).Offset(, 1) =
Cells(rw, cl)
Next
Rows(rw).Delete
End If
If Cells(rw, 1) = "" Then Rows(rw).Delete
Next
End Sub

 

[Ricky]

Option Explicit
Sub Combine()
  Dim rw As Long
  Dim cl As Long
  For rw = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Cells(rw - 1, 1) <> "" Then
        For cl = 1 To Cells(rw, Columns.Count).End(xlToLeft).Column
            Cells(rw - 1, Columns.Count).End(xlToLeft).Offset(, 1) =
Cells(rw, cl)
        Next
        Rows(rw).Delete
     End If
     If Cells(rw, 1) = "" Then Rows(rw).Delete
  Next
End Sub

Thanks Patrick and Tim. The macro Patrick is exactly what I was after
- tks

Ricky