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
|