MS Office Forum / Excel / Worksheet Functions / September 2007
sorting by character
|
|
Thread rating:  |
riccifs@tin.it - 17 Sep 2007 09:49 GMT Hi to everyone in the newsgroup, Does anyone to known how to sort a list by starting from any alphabetic letters? For example, if I chose "M", I'd like to have the entire list starting with all words that have as first letter "M" and after the other ones with "N" etc...
Thanks a lot, Stefano.
Sandy Mann - 17 Sep 2007 10:43 GMT With your data in columns A:E starting in row 1, something like this should do what you want:
Sub ReSort() Dim eNdRo As Long Dim cUtRo As Long Dim x As Long
eNdRo = Cells(Rows.Count, 1).End(xlUp).Row
With Range(Cells(1, 1), Cells(eNdRo, 5)) .Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With
For x = 1 To eNdRo If Left(Cells(x, 1).Value, 1) = Cells(1, 6).Value Then cUtRo = x Exit For End If Next x
Range(Cells(1, 1), Cells(cUtRo - 1, 5)).Cut Destination:=Cells(eNdRo + 1, 1)
eNdRo = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(cUtRo, 1), Cells(eNdRo, 5)).Cut Destination:=Cells(1, 1)
End Sub
 Signature HTH
Sandy In Perth, the ancient capital of Scotland and the crowning place of kings
sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk
> Hi to everyone in the newsgroup, > Does anyone to known how to sort a list by starting from any [quoted text clipped - 5 lines] > Thanks a lot, > Stefano. riccifs@tin.it - 17 Sep 2007 11:15 GMT > With your data in columns A:E starting in row 1, something like this should > do what you want: [quoted text clipped - 52 lines] > > Thanks a lot, > > Stefano. Sorry mate, but I'm not very good in how to manage a code in excel, so could you tell me better how to run it?
Thanks a lot, Stefano.
Sandy Mann - 17 Sep 2007 11:34 GMT Press the function key F11 with the Alt key held pressed. This will take you into VBA. Now select Insert > Module, this will insert a new white module. Copy the code I posted and paste it all into the white General module window.
Enter data in Columns A:E with the names that you want to sort by in Column A.
To run the code from a shortcut key select:
Tools > Macro> Macros The macro ReSort will be highlighted, select Options and enter a key in the Shortcut key box and click OK then Cancel in the Macro dialog box.
Now the Macro will run by pressing and holding the Ctrl key while you press your selected Shortcut key.
If the macro does what you want then post back with details of where you real data is, whether it has a header row and what other data you have on the sheet - especially where we can *park* the sorted data when we are cuting it.
 Signature HTH
Sandy In Perth, the ancient capital of Scotland and the crowning place of kings
sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk
>> With your data in columns A:E starting in row 1, something like this >> should [quoted text clipped - 61 lines] > Thanks a lot, > Stefano. riccifs@tin.it - 17 Sep 2007 12:03 GMT Thanks for your instructions, but the code does not to work, it just make an alphabetic order of column A. What I am doing wrong?
Stefano
Sandy Mann ha scritto:
> Press the function key F11 with the Alt key held pressed. This will take > you into VBA. Now select Insert > Module, this will insert a new white [quoted text clipped - 93 lines] > > Thanks a lot, > > Stefano. Sandy Mann - 17 Sep 2007 12:23 GMT Sorry I forgot to say, (can't you read minds? <g> ) put the letter, in the same that you want to start the sort on in cell F1.
I see as well that there is an unfortunate line break in the Newsgroup posting and I found that if the case of the letter in F1 is different to the case of the initial letter of the list then it errored out. This new code should take care of both of these things. Delete the code and paste this one in, it will ensure that there are no breaks where there shouldn't be:
Sub ReSort() Dim eNdRo As Long Dim cUtRo As Long Dim x As Long
eNdRo = Cells(Rows.Count, 1).End(xlUp).Row
With Range(Cells(1, 1), Cells(eNdRo, 5)) .Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With
For x = 1 To eNdRo If UCase(Left(Cells(x, 1).Value, 1)) = _ UCase(Cells(1, 6).Value) Then cUtRo = x Exit For End If Next x
Range(Cells(1, 1), Cells(cUtRo - 1, 5)).Cut _ Destination:=Cells(eNdRo + 1, 1)
eNdRo = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(cUtRo, 1), Cells(eNdRo, 5)).Cut _ Destination:=Cells(1, 1)
End Sub
Post back when you get it working.
(or if you don't of course!)
 Signature HTH
Sandy In Perth, the ancient capital of Scotland and the crowning place of kings
sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk
> Thanks for your instructions, but the code does not to work, it just > make an alphabetic order of column A. > What I am doing wrong? > > Stefano riccifs@tin.it - 18 Sep 2007 08:52 GMT > Sorry I forgot to say, (can't you read minds? <g> ) put the letter, in the > same that you want to start the sort on in cell F1. [quoted text clipped - 60 lines] > > > Stefano Hi Sandy, your code is simply great!! It works very well! You saved me...! Many, many thanks again for your help, I really appreciate that.
P.S. sorry for my late... but I have been very busy recently. Stefano.
Sandy Mann - 18 Sep 2007 09:08 GMT You're very welcome Stefano. Thank for the feed back.
 Signature Regards,
Sandy In Perth, the ancient capital of Scotland and the crowning place of kings
sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk
>> Sorry I forgot to say, (can't you read minds? <g> ) put the letter, in >> the [quoted text clipped - 72 lines] > sorry for my late... but I have been very busy recently. > Stefano.
|
|
|