PDA

View Full Version : Solved: Merging columns



maxflia10
03-11-2009, 04:42 PM
I have a sheet named Entries and names in column B3:B100 and in column D3 : D100. In the sheet named Sorted, I would like to have the names in a single column. Any help would be much appreciated.

TIA

Chris Bode
03-11-2009, 10:08 PM
well, not quite clear about the question,,,can not say without seeing the data,,though if you want to merge cells then,select the cells to be merged and click on merge and center button from the toolbar

GTO
03-11-2009, 10:43 PM
Greetings,

I agree with Chris. It is not overly clear as to what you are trying to do. I am guessing a bit differently - maybe that you want one long list from two lists.

Now I'm not sure what you want to do if some of the names are the same in both, but this should delete any duplicates and alpha-sort them.

In a standard module:
Sub Add_Sort()

Dim _
rngCol As Range, _
rngUni As Range, _
rCell As Range, _
myColl As Collection, _
i As Long

Set myColl = New Collection

myColl.Add Item:="DUMMY", Key:="DUMMY"

With ThisWorkbook.Worksheets("Entries")
Set rngCol = .Range("B3:B100")
Set rngUni = Application.Union(rngCol, .Range("D3:D100"))
End With

On Error Resume Next
For Each rCell In rngUni
If Not rCell.Value = vbNullString Then
For i = 1 To myColl.Count
If rCell.Value < myColl(i) Then
myColl.Add Item:=rCell.Value, Key:=CStr(rCell.Value), before:=i
End If
Next
myColl.Add Item:=rCell.Value, Key:=CStr(rCell.Value)
End If
Next rCell
On Error GoTo 0

myColl.Remove "DUMMY"

Set rngUni = ThisWorkbook.Worksheets("Sorted").Range("A1:A" & myColl.Count)

i = 0
For Each rCell In rngUni
i = i + 1
rCell.Value = myColl(i)
Next

End Sub

Does that help?

Mark

maxflia10
03-11-2009, 11:48 PM
Mark,

It's exactly what I want

Thanks

GTO
03-12-2009, 01:33 AM
Glad to help :-) If you mark the thread as Solved, it can help save others time checking for unanswered/unresolved stuff.

The Solved option is under the Thread Tools button right above your first post.

Have a great night,

Mark