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