On 10 Jul., 13:51, Ron Rosenfeld wrote:
On Fri, 10 Jul 2009 02:11:25 -0700 (PDT), andreas
wrote:





Dear Experts:
I got hundreds of addresses in an excel sheet which need to be
transposed. The addresses are all in one column and *arranged as
follows:

Name
Street
City
Tel
Blank row (1 to several)
Name Street
City
Tel
Blank row (1 to several)
etc. (another several hundred addresses more)

A macro should loop thru all these addresses and do the transposing
automatically. The addresses that are transposed should be placed
right next to each and every address.

Help is much appreciated. Thank you very much in advance. Regards,
Andreas

I don't know what you mean when you write

The addresses that are transposed should be placed
right next to each and every address.

But to transpose a column of address entries, that are located in, let us say,
A2:An, you could use this macro.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro
by name, and <RUN.

============================================
Option Explicit
Sub TranspAdr()
'Assumes every address group has at least two rows
'Does not test for this
Dim rSrc As Range, rDest As Range, c As Range
Dim i As Long

Set rSrc = Range("A2")
Set rDest = Range("B1")

i = 1
Do
* * Set rSrc = Range(rSrc, rSrc.End(xlDown))
* * rSrc.Copy
* * rDest(i, 1).PasteSpecial Transpose:=True
* * Application.CutCopyMode = False
* * Set rSrc = rSrc.End(xlDown).End(xlDown)
* * i = i + 1
Loop Until rSrc.End(xlDown).Row = Cells.Rows.Count
End Sub
=================================
--ron- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Ron,

did a couple of adjustments and now it is working as desired. Thank
you very much for your terrific help. Regards, Andreas