sorting by character

[riccifs]

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]

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[riccifs]

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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]

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.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[riccifs]

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:

 

[Sandy Mann]

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!)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[riccifs]

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!)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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]

You're very welcome Stefano. Thank for the feed back.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk