Help with macro to transpose data

C

Chuck Jurgens

I need to convert a column of data to rows. The data is in INconsistent
format, i.e., some records have 4 lines and some have 5 or 6 or 7 lines. It
looks like this:

Gourley Real Estate, Inc.
A Smaller Company, for Better Results.
4908 Deerwood Drive
Raleigh, NC 27612 Phone:(919) 788-9772
Fax:
Email: [email protected]
WebSite: http://www.michaelgourley.com


Holton Wilkerson - Broker, GRI
Leading The Way In Residential Real Estate
8312 Creedmoor Road
Raleigh, NC 27613 Phone:(919) 582-1638
Fax:(888) 580-8595
Email: [email protected]
WebSite: http://www.holtonwilkerson.com

Sunshine Realty
Buying or Selling ANY House...Glenda Gets It Right!
PO Box 40157
Raleigh, NC 27629 Phone:(919) 810-0742
Fax:(919) 779-5650
Email: [email protected]
WebSite: http://www.GetsItRight.com


Nariman Abar
CENTURY 21 VICKI BERRY REALTY
5312 Six Forks Rd Ste 21
Raleigh, NC 27609-4458 Office:(919) 782-9797
[email protected]


Marina Abbat
COLDWELL BANKER ADVANTAGE
7208 Falls Of Neuse Rd
Raleigh, NC 27615-3244 Office:(919) 847-2222
[email protected]


Charles R Abbott
C. ABBOTT PROPERTIES, INC.
7000 Harps Mill Rd STE103
Raleigh, NC 27615-3239 Office:(919) 844-6144

I would like for it to be transposed into horizontal format something like
this:

name address1 fax postcode telephone
name address1 address2 postcode telephone1 cell
name company address1 address2 fax postcode telephone

i.e., I don't care that the columns don't line up - I can take care of that.
I just can't transpose all of the data at one time. I would have to
individually cut and paste special w/ transpose.

I'm sure that this is an easy task for a person familiar with VB - but I'm
not. Can someone help me out?

TIA

Chuck
 
P

pikus

Public Sub AddressSort()
last = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
With Worksheets(1)
For x = last To 2 Step -1
If .Cells(x, 1).Value <> "" And _
.Cells(x - 1, 1).Value <> "" Then
.Range(.Cells(x, 1), .Cells(x, 10)).Cop
Destination:=.Range(.Cells(x - 1, 2), .Cells(x - 1, 2))
.Rows(x).Delete
End If
Next x
.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Let me know if you have any questions. - Piku
 
U

uniopp

Hi pikus,
I came accross this post while searching for a solution to the sam
problem as Chuck Jurgens.
Can you please explain how to use the code that you provided?
I'm using Excel 2000. Where do I put the code that you provided and ho
do I execute it?
Thank you
 
T

Tom Ogilvy

The code provided deleted some blank lines and deleted some of the data. At
least with the sample data in the original post.

It didn't transpose anything.

So it might not make any difference where to put it, but you would put it in
a general module.

Then go into

tools=>Macro=>Macros, select

AddressSort

and click Run

Do this on a copy of your workbook and use it on the copy if you don't want
to lose your data.
 
U

uniopp

OK, I understand how to run the code and I did get it to work on some o
my tests but I would like to understand what the code means and how t
modify it to suite my situation (e.g add more rows to the origina
address field sample provided by Chuck Jurgens).
Could someone please explain each line of the code and what it actuall
does. Pehaps someone could also reccommend a good (Basic) book tha
explains how to make excel macros?
Thank you
 
Top