Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = 1 To LastRow
Range("B" & x).Value = Mid(Range("A" & x).Value, 2, Len(Range("A" & x).Value))
Range("C" & x).Value = x
Next x
Range("B1:C" & LastRow).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For x = 1 To LastRow
Range("D" & x).Value = Application.WorksheetFunction.Match(x, Range("C1:C" & LastRow), 0)
Next x
End Sub
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | apple | anana | 4 | |||
2 | banana | angerine | 1 | |||
3 | orange | ango | 6 | |||
4 | mango | pple | 3 | |||
5 | cucumber | pple | 8 | |||
6 | apple | range | 5 | |||
7 | Zucchini | ucchini | 7 | |||
8 | Tangerine | ucumber | 2 | |||
Sheet1 |
Ponsy Nob. said:I still don't understand exactly what you need.
The code I posted merely ranks the list alphabetically and puts a ranking number in an adjacent column, but you are referring to cutting items from the list.
Please explain further.
It is probably better that you find someone to whom you can send the workbook, or make it available on the web.
(I presume that DRJ misunderstood your earlier post and that you need a normal alphabetically ranking - not a ranking based on the second letter only.)
i want to rank the words on the second letter of the word (after the first), then third etc
ie alphabetical order - the title of the thread
is this poss do you think?