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:
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:
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:
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!


--
angelface
------------------------------------------------------------------------
angelface's Profile: http://www.excelforum.com/member.php...o&userid=30820
View this thread: http://www.excelforum.com/showthread...hreadid=504856

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

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:
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:
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:
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!


--
angelface
------------------------------------------------------------------------
angelface's Profile:
http://www.excelforum.com/member.php...o&userid=30820
View this thread: http://www.excelforum.com/showthread...hreadid=504856

Hi,

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

Many thanks



--
angelface
------------------------------------------------------------------------
angelface's Profile: http://www.excelforum.com/member.php...o&userid=30820
View this thread: http://www.excelforum.com/showthread...hreadid=504856

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

Insert a module (menu InsertModule)

Copy the code in.

In Excel, menu ToolsMacroMacros...

Select Test in the list and click Run

Voila.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"angelface" wrote
in message ...

Hi,

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

Many thanks



--
angelface
------------------------------------------------------------------------
angelface's Profile:
http://www.excelforum.com/member.php...o&userid=30820
View this thread: http://www.excelforum.com/showthread...hreadid=504856