Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / September 2007

Tip: Looking for answers? Try searching our database.

sorting by character

Thread view: 
Enable EMail Alerts  Start New Thread
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.