Change a Column list into multiple rows & columns

[angelface]

Hi,

Please could any one help me with this task, I have tried various
functions including transpose without getting the layout that I need.

I have a list in a column of information that I would like to move to
multiple rows and columns,
i.e, the data below lists down in one column, I want to be able to have
one column of all the countries, one column for Embassy of, one column
for 1st line of address etc etc.. I have list what I want to achieve
below this list. However, sometimes there will be 9 rows of data for a
country and sometimes 11, some have website addresses and some dont, so
it does not follow a rule of 9 rows down etc.

Any Ideas????

AUSTRALIA
Embassy of Afghanistan in Canberra
PO Box 155
Deakin West ACT 2600
Tel: (+61-2) 6282 7311
Fax: (+61-2) 6282 7322
Email: (e-mail address removed)
Website: www.afghanembassy.net
Ambassador: T.B.A
Non-resident envoy to: New Zealand and Brunei Dar-El-Salam

AUSTRIA
Embassy of Afghanistan in Vienna
Lackierergasse 8 Top 9
1090 Vienna
Tel.: (+43-1) 524 7806
Fax: (+43-1) 524 7807
E-mail: (e-mail address removed)
Ambassador: H.E. Zia NEZAM
non-resident envoy to Hungary & Bosnia-Herzegovina

BANGLADESH
Embassy of Afghanistan in Dhaka
House No. CWN (C) - 2A
Road No. 24, Culshan, Dhaka
Tel.: (+8802) 989 5994
Fax: (+880-2) 988 4767
Email: (e-mail address removed)
Ambassador:* H.E. Akmal GHANI


To show like this:
Country Embassy Name Add1

AUSTRALIA Embassy of Afghanistan in Canberra PO Box 155
AUSTRIA Embassy of Afghanistan in Vienna Lackierergasse 8

BANGLADESH Embassy of Afghanistan in Dhaka House No. CWN


Thanks, any help much appreciated!

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Len(Cells(i - 1, "A").Value) <> 0 Then
Cells(i, "A").Resize(, 20).Copy Cells(i - 1, "B")
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete
Set rng = Nothing

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[angelface]

Hi,

Thank you for taking the time to look at my problem and for sending
response, however, I am not sure what to do with the functions you hav
sent. Can you explain a little further for me and tell what I need t
do.

Many thanks
:confused

 

[Bob Phillips]

It's VBA, so go to the VBIDE (Alt-F11)

Insert a module (menu Insert>Module)

Copy the code in.

In Excel, menu Tools>Macro>Macros...

Select Test in the list and click Run

Voila.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)