List Rows to Columns
 

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

List Rows to Columns
 

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

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

List Rows to Columns
 

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" wrote:

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

List Rows to Columns
 

On Sep 3, 4:13*pm, Patrick Molloy
wrote:
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" wrote:
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

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

Ricky