excel - Sorting Different Length Strings in VBA So That Longest Aren't Last -
i'm trying sort list of room numbers using vba. however, of rooms have "a" or "b" @ end of number denote suite style living. want sort list of rooms ones or b aren't sorted end of list because they're longer strings. tried sort function in vba so
startrange.sort key1:=sortrange, order1:xlascending where startrange , sortrange range objects defined elsewhere. function sorts data correctly, results such as
411 418 422 432 415a 415b 428b whereas i'd them sorted as:
411 415a 415b 418 422 428b 432 i thought trying sort rooms text counting first 3 characters , sorting each individual block of , bs depending on last letter, further complicated 3 things:
- some of rooms 4 digits (ex. 1195, 1045, etc). none of these rooms have issues or b.
- some of rooms prefaced "w" or "e" depending on if in west or east half of building. of these rooms have or b suffix, sorted correctly in list way them sorted (ex. w134, w135a, w135b, w136, , on).
- not of rooms end in same sheet in excel. i'm working master list of rooms several different buildings, , each sheet sorted building.
the format of cells has been both "general" , "text" , both have had same result. can offered appreciated in solving issue. thank in advance!
this following adaptation of what's on page
sub sort_special(startrange range, sortrange range) dim long sortrange = 0 9 .replace what:=i, replacement:="zzzzz" & i, lookat:=xlpart, matchcase:=false next end startrange.sort key1:=sortrange, order1:=xlascending, header:=xlno sortrange = 0 9 .replace what:="zzzzz", replacement:="", lookat:=xlpart, matchcase:=false next end end sub excel sorts numeric fields ahead of alphanumeric ones. replacing each digit dummy field in digit prefixed arbitrary string 'zzzzz' you're forcing excel treat fields alphanumeric. sort on these converted fields , afterwards remove each sequence of 'zzzzz'.
hopefully don't have room #s end 5 zs.
Comments
Post a Comment