Auto sort alphabetically using basic macro

**Tyler1226** · 09-18-2012, 02:12 PM

Hello,
I have scoured this and other excel forums looking for a simple macro code I can drop into my excel document to auto sort alphabetically based on a column, but have been unsuccessful. I have attached my worksheet to provide context to my question.

All I need is a macro that auto sorts all the rows alphabetically (descending) based on column C ("General Topic Categories") on sheet 1. Ideally the auto sort would occur once all the data in a row has been entered (i.e. column L), but I would be fine if it auto sorted after data was entered in column C in sheet 1. Also note that this will be be growing document, so the number of rows will always be increasing. It should be obvious but I don't want the headers sorted, so the first cell that should be alphabetized will be C2

I know this can easily be done using the sort function, but I will not the one imputing the data and the individual in charge would like it auto sorted.

Thank you in advance for your help

**Chippy** · 09-18-2012, 05:56 PM

Try this code in the Sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)

    'If column L (12) has just been entered and cells A-L on the same row are populated then sort all the data by descending column C
    
    If Target.Column = 12 And Application.WorksheetFunction.CountA(Range("A" & Target.Row & ":L" & Target.Row)) = 12 Then
        Cells.Sort Key1:=Range("C1"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End If
    
End Sub

**Tyler1226** · 09-18-2012, 06:29 PM

Hello Chippy
Thanks for the help, but unfortunately it's not working

Also, not every cell in a row will be populated. Any additional help would be greatly appreciated. In reality, and to make things easier, I just need the rows to auto sort alphabetically based on data in column C.

Thanks

**Chippy** · 09-18-2012, 06:37 PM

Try deleting the "And Application.WorksheetFunction.CountA(Range("A" & Target.Row & ":L" & Target.Row)) = 12". Then the sort will occur after column L has been entered, regardless of any other cells on the row.

**Tyler1226** · 09-19-2012, 02:18 PM

Hello Chippy,
I GOT IT TO WORK! Thank you so much. I actually changed the code a tiny bit so that the sorting occurs when data is entered into column C (so it now says "If Target.Column = 3"). One final question, is there a way so the screen/selected cell moves with the recently alphabetized row? Currently, when data is entered into column 3 everything get alphabetized (which is great), but the selected cell stays at the bottom. I would like the selected cell to move with its row. My concern is that if there is a lot of rows, the row you are entering data into could get lost. Thanks

**Chippy** · 09-20-2012, 04:18 PM

In order to know which row the input cell (currently selected cell in column C) has moved to after the sort, a little trick is needed. A temporary 'marker' is put in a spare column cell (I've used M) of the input row, and after the sort we look for the marker on its new row and select the C cell on that row. Here is the new code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim inputRowCell As Range
    
    'If column C (3) has just been entered
    
    If Target.Column = 3 Then
        
        'Put temporary marker text in cell M of input row
        
        Application.EnableEvents = False
        Cells(Target.Row, "M").Value = "INPUT_ROW"
        Application.EnableEvents = True
        
        'Sort data on descending column C
        
        Cells.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        
        'Find marker text in column M
        
        Set inputRowCell = Columns("M").Find(What:="INPUT_ROW", After:=Range("M1"), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
            
        'Clear marker text and select cell C of new input row
        
        Application.EnableEvents = False
        inputRowCell.Clear
        Application.EnableEvents = True
        Cells(inputRowCell.Row, "C").Select

    End If
    
End Sub