MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Gene DeLallo on July 20, 2000 2:25 PM

I have three columns-in A I have 100 names,in column B I
have 100 names and in column C I have 100 names. Is there any
way I can sort the three colums so I can get all the 300 names in alphabetical


Posted by Ada on July 21, 0100 2:03 AM

If you have data in column A below row 100, the macro I wrote would need to be revised.
The following macro would do it - please also note that this macro could be created without any knowledge of VBA - it could be created by using the macro recorder(altho the recorded code would be a bit longer than the code below).
Both macros assume that the maximum number of rows with names is 100.

Sub SortNames()
Columns("A:A").Insert Shift:=xlToRight
Range("B1:B100").Cut Destination:=Range("A1")
Range("C1:C100").Cut Destination:=Range("A101")
Range("D1:D100").Cut Destination:=Range("A201")
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
Range("A1:A100").Copy Range("B1")
Range("A101:A200").Copy Range("C1")
Range("A201:A300").Copy Range("D1")
End Sub

To create the above macro with the macro recorder, the steps to record are :-

Select column A and insert a column.
Cut B1:B100 and paste to A1:A100.
Cut C1:C100 and paste to A101:A200.
Cut D1:D100 and paste to A201:A300.
Select column A and sort column A only.
Copy A1:A100 and paste to B1:B100.
Copy A101:A200 and paste to C1:C100.
Copy A201:A300 and paste to D1:D100.
Delete column A.

Please note that the resulting macro is inflexible in that it is operating on the fixed ranges per your message.

If you need a more flexible macro, please advise.


Posted by Gene DeLallo on July 21, 0100 6:21 AM

Thanks for the info. Could you also give me a more
flexible macro. Also is there a way of doing it without a macro.
Thanks again

Posted by Ada on July 21, 0100 6:40 AM


The only way I can think of doing it without a macro is to do the steps set out in my previous message. There could well be some other shorter way.

To do a more flexible macro, need more info:-

1.Are the number of rows in each column variable?
2.If the rows are variable, do you want to keep the original number of rows or do you want to reset the rows to some other number?
3.Are the number of columns variable?
4.Are there any blank cells between names?
5.Are there any cells with data other than the names and if yes, where are they?

Does the previous macro produce the required result?


Posted by Gene on July 24, 0100 7:01 AM

Posted by Ada on July 25, 0100 7:54 PM

The following assumes that the column headings are in row 1, and the names start in row 2 :-

Sub SortNames()
Dim notSorted As Range
Set notSorted = Range("B2:B101")
notSorted.Copy Destination:=Range("A65536").End(xlUp).Offset(1, 0)
Set notSorted = notSorted.Offset(0, 1)
Loop Until notSorted(1, 1) = ""
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Range("A102:A201").Copy Range("IV2").End(xlToLeft).Offset(0, 1)
Loop Until Range("A102") = ""
End Sub


Posted by Ada on July 20, 0100 8:44 PM

Here's one way :-

Sub SortNames()
Range("B1:B100").Cut Destination:=Range("A65536").End(xlUp).Offset(1, 0)
Range("C1:C100").Cut Destination:=Range("A65536").End(xlUp).Offset(1, 0)
Range(Range("A1"), Range("A65536").End(xlUp).Offset(1, 0)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
Do Until Range("A101") = ""
Range("A101:A200").Copy Range("IV1").End(xlToLeft).Offset(0, 1)
End Sub