Gord,
Your in my prayers!
Fast Effecient Thoughtful
and able to solve the problem!!!!!!
Gord for President!
Regards
Lowell
Gord Dibben <gorddibbATshawDOTca wrote in message . ..
Lowell
To get into 3 or 4 columns per address.
Remember!! Always experiment on a copy of the sheet or book.
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)).Copy _
Destination:=.Range(.Cells(x - 1, 2), .Cells(x - 1, 2))
Rows(x).Delete
End If
Next x
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End With
End Sub
Copied to a general module in your workbook.
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run the macro by going to ToolMacroMacros.
Gord Dibben Excel MVP
On 29 Nov 2004 16:01:44 -0800, (lucky_lowell) wrote:
I have a flat text file that looks like this
Mr and Mrs Smith
19 Elm Street
Boston, Ma 01231
Mark Hanson
Kathy Elmstead
13 sycamore drive
Bolston Mt 91320
Mark and Linda Everett
342 Pine Street
Austin, Tx 53221
it goes on for about 500 entries
I'd love to get it into this format
Mr and Mrs Smith 19 Elm Street Boston Ma 01676
etc
any clever ideas
thanks
Lowell