Forum Discussion
Solved
How do I merge 2 columns
Ok so basically its a bit more complicated than that, I want to merge a phone number and home phone number into one column , but I want the home phone number to go to the next line in the merged colu...
Run the following macro:
Sub MergePhone() Dim rng As Range Application.ScreenUpdating = False Set rng = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious) Do rng.Offset(1).EntireRow.Insert rng.Offset(1, -2).Value = rng.Offset(0, -2).Value rng.Offset(1, -1).Value = "'" & rng.Value Set rng = Range("C:C").Find(What:="*", After:=rng, SearchDirection:=xlPrevious) If rng.Row = 1 Then Exit Do Loop Range("C:C").ClearContents Application.ScreenUpdating = True End Sub
Run the following macro:
Sub MergePhone()
Dim rng As Range
Application.ScreenUpdating = False
Set rng = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious)
Do
rng.Offset(1).EntireRow.Insert
rng.Offset(1, -2).Value = rng.Offset(0, -2).Value
rng.Offset(1, -1).Value = "'" & rng.Value
Set rng = Range("C:C").Find(What:="*", After:=rng, SearchDirection:=xlPrevious)
If rng.Row = 1 Then Exit Do
Loop
Range("C:C").ClearContents
Application.ScreenUpdating = True
End Sub- Hey Hans, If I want to merge 6 columns instead of just 2 what would i need to change??
You've never said "Why" you are doing these merges. In general, merging data like that is something people regret later, since Phone# becomes ambiguous once done....whereas "Cell" and "Home" are clear. I can't imagine why you'd want to merge six, unless you're talking the whole home address, and there too I'd want to ask, "Why?!!"
If you're doing this so as to create a printout of each person's contact info, or a nice address list, you'd be far better served, over the long term, by keeping each data element intact as separate cells and using MailMerge in Word (with the Excel database as the source) to create the printouts.
- Thank You Soo Much!! This is exactly what I needed!!
