Automatic Sorting - Alphabetically

[sean]

I was wondering if it is possible to automatically sort a column alphabetically.

Like if I have a list:
ace
bat
cat

and then if I add the word "abs" then the word will automatically go above the word ace.

Is this possible? Thank you in advance,
Sean

[ This Message was edited by: sean on 2002-04-08 06:34 ]

[Derek]

Hi Sean

You could try something like this event macro

Right click sheet tab, left click View Code and paste this code in the white area.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error Resume Next
If Target.Value <> "" Then
Target.EntireColumn.Select
Selection.Sort Key1:=Range(Target.Address), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Target.Select
End If
End Sub

This sorts by the column you are using
regards
Derek




[ This Message was edited by: Derek on 2002-04-08 06:55 ]

[ This Message was edited by: Derek on 2002-04-08 06:56 ]

[Al Chara]

You can use VBA. Try the following code in the code for your worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.Sort Key1:=Target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

[Derek]

Hi again
I think Al Chara's answer is better than mine.
regards
Derek

[sean]

This code works great, but how do I make it so that it targets only one column like column B for example?

Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.Sort Key1:=Target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Thanks again everyone I really appeciate it.

[Derek]

Hi

Put this line at start of the code:
If Target.Column = 2 Then

and put this line at the end of the code:
End If

regards
Derek

[sean]

Sorry I have one more small problem. My list starts in A2 so when I make an entry below then it pushes my list into A1. Is there a way to correct this? Last question I promise!

Thanks again,
Sean

[Derek]

Hi Again

Yes, change the code to this. Since you have specified column A then I have restricted it to the range A2:A1000. Change this if you go beyond 1000 rows

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
'Target.EntireColumn.Sort Key1:=Target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2:A1000").Sort Key1:=Target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

regards
Derek

[sean]

Yep, you've guessed it. I honestly have one more question. I have appreciated your help thus far beyond words.

Is it possible to edit the code so that only columns B and C sort but not A,D etc.? or maybe columns B,C and D? etc.

Thank you VERY MUCH in advance,
Sean

[Derek]

Hi again Sean

Sorry for delay, was getting late in Australia and needed the beauty sleep.

Adapt this code to your needs (I have selected columns 1,2,3)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Range
Set x = Cells(2, Target.Column)
Dim y As Range
Set y = Cells(1000, Target.Column)

If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then
Range(x, y).Sort Key1:=Target, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

All the best
regards
Derek