Automatic Sorting - Alphabetically
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Automatic Sorting - Alphabetically

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com