View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Joanne[_4_] Joanne[_4_] is offline
external usenet poster
 
Posts: 29
Default multi line cells

Len
Thank you so much for your help. This is a mailing list that I am
working on that was created by putting the info in separate cells (I
know not why, being new on the block).

So a one time app is good and since I only need to get them into the
multi-line cell format (BYW, I googled and found out that the
alt+enter combo will let me put multi lines in manually) and that is
the only use for the spreadsheet, I was thinking that while either way
will work I would like to use the sub routine to move them (a little
practice in Excel VBA is something I need) into row B and then just
delete row A, using this much of your coding:

Sub CombineAddresses()

Columns("B:B").WrapText = True
Range("B1").Activate
Do While Len(Trim(ActiveCell.Offset(0, -1).Value)) 0
ActiveCell.Value = ActiveCell.Offset(0, -1).Value & vbLf & _
ActiveCell.Offset(1, -1).Value & vbLf & _
ActiveCell.Offset(2, -1).Value
ActiveCell.Offset(4, 0).Activate
Loop
Columns("B:B").Select
Selection.Copy

End Sub

Am I correct that this is enough code to do the job?

Can you point me to some on-line resources for questions like mine -
Now that I am started, I think I'll noodle around with Excel and see
what else I can clean up quickly.

Again, thanks a million for your time and expertise - you have started
my day off on an up note.

Joanne

LenB wrote:

If you only need to do this once, or not very often, it can be done with
a formula. For example, format column B to have wordwrap on, and put
this formula in B1, then copy/paste into every fourth cell in B, or fill
down by selecting B1 thru B4 and dragging down the fill box.

=A1 & CHAR(10) & A2 & CHAR(10) & A3

(Char(10) is linefeed, and formatting with wordwrap recognizes it)
Then you could paste the values from B onto another sheet and remove the
blanks. Then you still have your original data in a much better form,
as stated by NickHK.

Having said that, here's one way to do this with a macro, for
educational purposes only ;-)
It will overwrite the current sheet column b, and sheet2 column A.
Start it with the data sheet active.

Sub CombineAddresses()

Columns("B:B").WrapText = True
Range("B1").Activate
Do While Len(Trim(ActiveCell.Offset(0, -1).Value)) 0
ActiveCell.Value = ActiveCell.Offset(0, -1).Value & vbLf & _
ActiveCell.Offset(1, -1).Value & vbLf & _
ActiveCell.Offset(2, -1).Value
ActiveCell.Offset(4, 0).Activate
Loop
Columns("B:B").Select
Selection.Copy
'change "Sheet2" below to where you want the output...
Worksheets("Sheet2").Activate
Range("A1").Activate
ActiveSheet.Paste
Range("A1").Select
Do While Len(Trim(ActiveCell.Value)) 0
'delete the blank cells. There is probably a more
'elegant way to do this but ....
ActiveCell.Offset(1, 0).Delete shift:=xlUp
ActiveCell.Offset(1, 0).Delete shift:=xlUp
ActiveCell.Offset(1, 0).Delete shift:=xlUp
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

HTH
Len


Joanne wrote:
I have a contact file in excel that has the data all in individual
cells and I need to get it into 3 lines of info per cell.

Here is the scenario

A1 John Doe
A2 110 Main Street
A3 Anytown, USA

I need to get into this format:
A1 John Doe
110 Main Street
Anytown, USA

A2 Jane Doe
110 Main Street
Anytown, USA

etc etc for approx 600 Contacts

I wanted to do a macro to get me started, but I don't know how to cut
and paste the information from the individual cells into one cell, so
I am stymied right from the get go. I am quite familiar with VBA in
MSWord 2003 and a bit with Outlook, but have no experience at all in
Excel.

Am using MSOffice Pro 2003 on WinXP Pro - the file consists of 3 line
addresses with one blank row in between each contact.

Any help or direction towards help will sure be appreciated.

Thanks
Joanne