Sort Text and Numbers:

Re: Sort Text and Numbers:

Well, it's the same problem as the precedent macros. Civic numbers belong to their respective street and those streets to their repective sectors. So sectors are sorted. Then among a specific sector (for each sector) streets belonging to this sector are sorted inside that sector, and the same things apply to civic numbers sorted inside their specific street. It's exactly the same principle we've done previously. In fact, the last macro you've written does exactly what I just described. The only problem is that the first column is not numbers to be sorted but names in letters. It's only a matter of changing the type of characters to be sorted in the first column in this last macro.

Re: Sort Text and Numbers:

The standard Excel sort does what you want (for up to three columns at a time). To do it for the selection (with no headers) with a macro, you can replace all of the earlier code with
[vba]Sub SortThis()
With Selection
.Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 2), order2:=xlAscending, _
key3:=.Cells(1, 3), order3:=xlAscending, header:=xlNo, Orientation:=xlTopToBottom
End With
End Sub
[/vba]

Re: Sort Text and Numbers:

Yay! Thanks Derks Simplier code. I didn't know Excel was able to do it. Well I am glad it actually does. Your code works perfectly!

Oh and hi Ross! Your thread has done a long run into sorting macros since your last post hehe. For your own problem, the way I see it, you would need to find the lenght of the last name until you find an empty space. The problem is if you have a second last name without any space.
(E.g. Delano Roosevelt Franklin = Roosevelt Franklin Delano). It's the way I see it. There might be an other way, but that's the one I see personnally.

Regards,

Werner

Re: Sort Text and Numbers:

Hi Ross,
Try the following modification for sorting last names. Note that Excel 2000 or later is required. The code will not sort correctly for people with Jr. and similar things after their name, but could be modified to account for that. as with the code above, it functions on the selection and assumes no header rows.
[vba]Sub SortbyLast()
Dim r As Range, c As Range
Application.ScreenUpdating = False
Set r = Selection
r.Columns(2).Insert xlToRight
For Each c In r.Columns(1).Cells
c.Offset(0, 1) = Mid(c, InStrRev(c, " ") + 1) 'InStrRev only in Excel 2000 and later
Next c
r.Resize(, r.Columns.Count + 1).Sort key1:=r.Cells(1, 2), order1:=xlAscending, _
key2:=r.Cells(1, 1), order2:=xlAscending, _
header:=xlNo, Orientation:=xlTopToBottom
r.Columns(2).Delete xlToLeft
Application.ScreenUpdating = True
End Sub
[/vba]