Automatic Sorting - Alphabetically

sean

New Member
Joined
Apr 7, 2002
Messages
9
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top