excel macro to automatically put rows in alphabetical order

wally32

New Member
Joined
Mar 7, 2011
Messages
29
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Mar 7, 2011
Messages
29
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Mar 7, 2011
Messages
29

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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)

Code:
 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!
 
Last edited:

wally32

New Member
Joined
Mar 7, 2011
Messages
29

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

Code:
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
Code:
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
Code:
worksheetfunction.counta
performs a check to make sure all data is entered.

Code:
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.
 
Last edited:

wally32

New Member
Joined
Mar 7, 2011
Messages
29
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

New Member
Joined
Mar 7, 2011
Messages
29
if there are any other websites or link that may be handy, could you please copy me in the links

cheers
 

Forum statistics

Threads
1,141,018
Messages
5,703,754
Members
421,313
Latest member
Mooncake1

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
Top