VBA Sort Street Address Without Helper Column

[Stephen_IV]

Good Morning,

I am trying to sort street addresses. But what I would like to do is not use a helper column to get this done. Can anyone please help me out with some code that will help with this! Thanks in advance.

PS the street addresses are cleaned up so they will always start with a number followed by the street name.

Thanks in advance!

Book1
	A	B	C	D	E	F
1	Student #	Last Name	First Name	School	Gr	Street Address
2	100	xxxxxxxxx	xxxxx	xxxx	xx	2 Acorn Drive
3	101	xxxxxxxxx	xxxxx	xxxx	xx	10 Kobe St
4	102	xxxxxxxxx	xxxxx	xxxx	xx	1020 Madison Ave
5	103	xxxxxxxxx	xxxxx	xxxx	xx	1952 East Main St
6	104	xxxxxxxxx	xxxxx	xxxx	xx	3883 Jones Ave
7	105	xxxxxxxxx	xxxxx	xxxx	xx	4 Kent Court
8	106	xxxxxxxxx	xxxxx	xxxx	xx	41 Main Street
9	107	xxxxxxxxx	xxxxx	xxxx	xx	423 Barnum Ave
10	108	xxxxxxxxx	xxxxx	xxxx	xx	5 Alba Ave
11	109	xxxxxxxxx	xxxxx	xxxx	xx	67 State St
12	110	xxxxxxxxx	xxxxx	xxxx	xx	22 Kent Court
Sheet1

To this.

Book1
	A	B	C	D	E	F
1	Student #	Last Name	First Name	School	Gr	Street Address
2	100	xxxxxxxxx	xxxxx	xxxx	xx	2 Acorn Drive
3	108	xxxxxxxxx	xxxxx	xxxx	xx	5 Alba Ave
4	107	xxxxxxxxx	xxxxx	xxxx	xx	423 Barnum Ave
5	103	xxxxxxxxx	xxxxx	xxxx	xx	1952 East Main St
6	104	xxxxxxxxx	xxxxx	xxxx	xx	3883 Jones Ave
7	105	xxxxxxxxx	xxxxx	xxxx	xx	4 Kent Court
8	110	xxxxxxxxx	xxxxx	xxxx	xx	22 Kent Court
9	101	xxxxxxxxx	xxxxx	xxxx	xx	10 Kobe St
10	102	xxxxxxxxx	xxxxx	xxxx	xx	1020 Madison Ave
11	106	xxxxxxxxx	xxxxx	xxxx	xx	41 Main Street
12	109	xxxxxxxxx	xxxxx	xxxx	xx	67 State St
Sheet2

 

[Joe4]

Without a helper column, it is going to sort by the whole value, including the numbers. I don't think there is any way to tell Excel's Sort functionality to ignore part of the value for a single cell in sorting.

Perhaps you could split your street column into two column?
One for the street number, and one for the street name. That would then allow you to sort like you want it to.

 

[MickG]

Try this:-
Results start "G1".

[COLOR="Navy"]Sub[/COLOR] MG20Jul11
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ray     [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] i       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] itxt    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] jtxt    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp(1 To 6) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Ray = Rng.Resize(, 6).Value
[COLOR="Navy"]For[/COLOR] i = 1 To UBound(Ray)
    [COLOR="Navy"]For[/COLOR] j = i To UBound(Ray)
        itxt = Mid(Ray(i, 6), InStr(Ray(i, 6), " ") + 1, Len(Ray(i, 6)) - InStr(Ray(i, 6), " ") + 1)
        jtxt = Mid(Ray(j, 6), InStr(Ray(j, 6), " ") + 1, Len(Ray(j, 6)) - InStr(Ray(j, 6), " ") + 1)
        [COLOR="Navy"]If[/COLOR] jtxt < itxt [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] n = 1 To 6
                temp(n) = Ray(i, n)
                Ray(i, n) = Ray(j, n)
                Ray(j, n) = temp(n)
            [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] j
[COLOR="Navy"]Next[/COLOR] i
Range("G1").Resize(UBound(Ray, 1), UBound(Ray, 2)) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Joe4]

Yes, I should have qualified my statement to say I don't think it can be done "without the use of VBA", as I don't think you can change how the native Excel Sort functionality works.

Basically, you would need to create your own sorter in VBA, like Mick has done (unless you could split the column like I mentioned, or use a helper column, which you do not want to do).

 

[Stephen_IV]

Mick you are right on the money! It works great! Thank you again! Much appreciated!