excel macro to automatically put rows in alphabetical order

[wally32]

hi

Hoping someone can possibly help me to create an excel macro for a speadsheet im trying to create.

I am looking to create a spreadsheet where users will be update continually and the information automatically goes in alphabetical order, to save having to go data sort asecnding constantly.

the spreadsheet will constant of 6 columns A-F and row A1 - F1 will be header columns. when user enters the information into the columns and keys enter, i need that particular row to go in alphabetical order based on the information keyed in Column A.

Im quite a newbie at this, and have looked at several other threads but the codes on there do not seem to work or would do what i am looking for.

thanks

 

[Weaver]

So what have you tried and discounted so far?

I'm assuming you would'nt want to sort the data until all 6 cells had been completed.

I guess the way to go would be to test after a cell change to see if the current row is complete and if it is, then sort the data, including the new row.

 

[wally32]

Thanks for the response Weaver, You are correct. Would not want the to sort the date until the 6 columns are completed.

I will have a look at those links you sent now and hopefully they will shed a bit more light on it for me

 

[Weaver]

The links are just part of my signature - although that's not to say you won't find them useful. Working on a solution right now.

 

[wally32]

the links were very useful, i have managed to record and run a macro to sort, but this would mean having to run it everytime, where i would need for it to do it automatically when the last information in column F is completed.

The macro that i have come up with is

Sub SortAlphabeticallyOrder()
'
' SortAlphabeticallyOrder Macro
' Macro recorded 08/03/2011 by waltek
'
'
Range("A1:F1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=60
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Thx

 

[Weaver]

Right click the tab of the sheet you have your data in, then choose 'view code' from the menu

Paste the following into the large pane in the centre of the screen (here's where the links might come in handy if you're unsure of what to do)

 Private Sub Worksheet_Change(ByVal Target As Range)
   If WorksheetFunction.CountA(Cells(Target.Row, 1).Resize(1, 6)) = 6 And Target.Column < 7 Then
        Application.EnableEvents = False
            Range("A1", Cells(Rows.Count, 6).End(xlUp)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
                xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
        Application.EnableEvents = True
    End If
End Sub

Now, if you enter a new line of data, once there are 6 completed cells in A - F, the new data table will be sorted. Also, if you change any of the previous records in column A, the data will be resorted.

If blanks are a possible valid entry, we may need to rethink the trigger conditions.

BTW I learned everything I know about macros from this site and from the 'record macro' function, so you're starting in the right place!

 

[wally32]

fantastic. That has worked. Could you explain so i can fully understand the process where you come up with the code you provided. Does this code work in line with what i done with the initial record macro code, or is the code i created not needed anymore.

 

[Weaver]

I did something very similar to you in order to get the syntax right for the sort command - beats having to remember it. One thing worth noting is that you can always delete the scroll commands - that's just visual.

The dynamic sort range comes next

Range("A1", Cells(Rows.Count, 6).End(xlUp))

gives us a range starting from A1 in the top left to the last cell with a value in it from column 6 and then applies the sort to this area

Cells(Rows.Count, 6).End(xlUp)

If you selected the last row of column F and pressed ctrl up arrow, you'd get the same effect.

The

worksheetfunction.counta

performs a check to make sure all data is entered.

Application.EnableEvents = False

prevents the code from triggering itself when the code effectively changes the worksheet, which you wouldn't get from recording a macro.

Come to think of it, you'd never get event driven code of any kind via macro recording, as far as I can tell.

PS you don't need your code any more.

PPS another useful side effect of this implementation is that if you miss a row or 2 when entering your new data, it'll still work - try it.

 

[wally32]

ok, i can see it now.

Many Thanks weaver. your help greatly apprecaited. Im sure i can find some more macros to do which will make my life that little bit easier.

 

[wally32]

if there are any other websites or link that may be handy, could you please copy me in the links

cheers