Sorting

Gene
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")
Columns("A:A").Delete
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.

Ada

Ada
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
Gene

Gene

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?

Ada

Gene
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")
Do
notSorted.Copy Destination:=Range("A65536").End(xlUp).Offset(1, 0)
notSorted.ClearContents
Set notSorted = notSorted.Offset(0, 1)
Loop Until notSorted(1, 1) = ""
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Do
Range("A102:A201").Copy Range("IV2").End(xlToLeft).Offset(0, 1)
Range("A102:A201").EntireRow.Delete
Loop Until Range("A102") = ""
End Sub

Ada

Gene
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)
Range("A101:A200").EntireRow.Delete
Loop
End Sub

Ada