Ok, before any one shoots me down, i have looked around the forum and the internet for a answer, Im im half way there.
I have a column in my worksheet which contains alphanumeric data, I also have a Custom menu option to sort the worksheet but 3 columns.
No i know if i have a column contains the following
1
1
2a
3b
3c
2
and i have set the cells to text then it would sort as follows
1
1
2
2a
3c
3b
Which is cool but i have a colum looking like this
3
3
3
8
8a
8b
65a
65
71
71
and when sorted looks like this
3
3
3
65
65a
71
71
8
8a
8b
this is because excel is using the first digit, but i need it to sort as whole numbers so it would look like below
3
3
3
8
8a
8a
65
65a
71
71
Any ideas, I need this to be included in teh VBA code i have for sorting
Sub SortSpecial()
Dim FirstRow As Long, EndRow As Long, LastRow As Long
ActiveSheet.Protect Password:="gideon", UserInterfaceOnly:=True
LastRow = Range("B" & Rows.Count).End(xlUp).Row
FirstRow = Range("D1").End(xlDown).Row
Do
If FirstRow > LastRow Then Exit Sub
EndRow = Range("D" & FirstRow).End(xlDown).Row
Range("B" & FirstRow, "P" & EndRow).Sort Key1:=Range("D" & FirstRow), Order1:=xlAscending, _
Key2:=Range("E" & FirstRow), Order2:=xlAscending, _
Key3:=Range("H" & FirstRow), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
FirstRow = Range("D" & EndRow).End(xlDown).Row
Loop
End Sub
Thanks G
Bookmarks