Loading
ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Automatically Sorting as You Enter Information

Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatically Sorting as You Enter Information.

Pat wonders if there is a way to automatically sort every time she adds new data to a worksheet. Pat thinks it would be great, for instance, that when she adds a new name to a list of names that the names are automatically sorted to always be in order.

The only way that this can be done is by using a macro that is triggered whenever something new is entered in the worksheet. You can, for instance, add a macro to the code for a worksheet that is triggered when something in the worksheet changes. (You can view the code window by right-clicking the worksheet tab and choosing View Code from the resulting Context menu.) The following is an example of one such simple macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Range("A1").Sort Key1:=Range("A2"), _
      Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub

The macro assumes that you want to sort on the data in column A and that there is a header in cell A1. If the names are in a different column, just change the cell A2 reference to a different column, such as B2, C2, etc.

Of course, sorting anytime that any change is made can be bothersome. You might want to limit when the sorting is done so that it only occurs when changes are made to a specific portion of your data. The following version of the macro sorts the data only when a change is made in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("A1").Sort Key1:=Range("A2"), _
          Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub

There are some drawbacks to using a macro to automatically sort your data. First, since you are using a macro to sort, the operation is essentially "final." In other words, after the sorting you can't use Ctrl+Z to undo the operation.

A second drawback is that data entry might become a bit disconcerting. For instance, if you use any of the above macros and you start to put names into the worksheet, they will be sorted as soon as you finish what is in column A. If your data uses five columns and you start your entry in row 15, as soon as you get done entering the name into column A (and before you enter data into columns B through E), your data is sorted into the proper order. This means that you will need to find where it was moved in the sort, select the proper cell in column B, and then enter the rest of the data for the record. Of course, the way around this is to add your data in an unnatural order—simply make sure that the name in column A is the very last thing you enter for the record.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9006) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Automatically Sorting as You Enter Information.

Related Tips:

Got the Time? Understanding the ins and outs of working with times and dates can be confusing. Remove the confusion--ExcelTips: Times and Dates is an invaluable resource for learning how best to work with times and dates. Check out ExcelTips: Times and Dates today!

 

Comments for this tip:

nitin    29 Mar 2014, 11:39
Hi!
Got the solution.
But 2 queries:
1. What does Key1:=Range("A2") refer to?
2. The regional settings on my laptop are Indian but the date format (which was earlier accepting indian format of input viz. dd-mmm-yyyy) is now accepting only american format - ie: mm-dd-yyyy but is displaying in dd-mmm-yyyy format.
eg.: if i enter "2-1" the date automatically converts to "01-feb-2014"
& if i enter "23-5" it converts to "23-05-2014" but if i enter "5-23" it converts to "23-Jan-2023"

Can you please guide to resolve this?
BTW, I forgot to mention that the sorting is done on the date column
the macro i use is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("d17:d1000")) Is Nothing Then
        Range("d17:i1000").Sort Key1:=Range("d1"), _
          Order1:=xlAscending, Header:=x1Yes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub


NITIN    29 Mar 2014, 03:27
Hi! I tried it & is working but i have a query:

if i have the data header in D5
I enter anything in D6 to D100, then the range D6 to I100 should be sorted on D
the columns J onwards should not change as they contain formulae.
Steph    06 Mar 2014, 12:02
@Barry

Nevermind. Thanks for your help. I ended up getting it to work with this:

Public RowNo As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Row <> RowNo Then
        RowNo = Target.Row
        Range("B1").Sort Key1:=Range("B2"), _
          Order1:=xlDescending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub

Steph    06 Mar 2014, 09:30
This is what i have in there:


Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Range("B1").Sort Key1:=Range("B2"), _
      Order1:=xlDescending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub


Thanks!
Barry Fitzpatrick    05 Mar 2014, 05:31
@Steph,

It looks as though the macro is not being triggered by the change you are making, or if triggered the second parameter for the "Intersect" test is causing the macro to exit before sorting.

I'm not sure which macro you are using could you paste it into a comment?
Steph    04 Mar 2014, 08:03
I have a spreadsheet (excel 2010) where the sheet I want to auto sort largest to smallest (numbers) from the data in column B. I put in this code and it works when I add a new piece of data but it wont work when the data already there changes. All the data in the cells in question are copied from another cell on another sheet (It is where all the extrapolated data from all the individual sheets goes) is there a way to get around this? It works when I manually sort the data and when I add just a plain number to the bottom but for some reason wont work when the data in the cell changes. Thanks
Barry Fitzpatrick    03 Mar 2014, 05:17
@Willy

The first macro should be triggered on a change in the selection.

If you take the second macro if entry of the first name to completed by pressing 'Enter' AND "Enter" is set to move to the cells below (default) then then yes I agree this triggers sorting (as designed) if however the user uses TAB or the mouse to click to the cell to the right then sorting will not be triggered. Sorting is only triggered when the row in which the active cell is located is changed.
It is common data entry practice to TAB between field entries and use Enter to complete the record entry.

To be honest my preferred solution would be to use a Userform, and apply some validation before adding the entry to the main table and subsequently sorting the table. The validation would check for all mandatory fields to be completed, check for illegal characters, etc etc.
Willy Vanhaelen    02 Mar 2014, 11:45
@Barry
When you make an entry in column A, the proposed macros in this tip do sort the table and your entry seems to disappear which can be very disconcerting. Neither of your two proposed solutions solves this.

The macro I proposed here on 17 November copes with it. When you enter a name in column A it immediately gets sorted but the cursor jumps to the new position and you can instantly continue entering data in the same row.

Barry Fitzpatrick    01 Mar 2014, 11:06
Another way is to execute the sorting when the last column has data entered into it.

So say if the last column was J then the macro would become:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
        Range("A1").Sort Key1:=Range("A2"), _
          Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub

This presupposes that data is entered into this column if it is an optional field then the data will not change and the macro sorting the data will not be triggered to run.

Another way would be to sort only if the row value changed:

Public RowNo as Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Row <> RowNo Then
        RowNo = Target.Row
        Range("A1").Sort Key1:=Range("A2"), _
          Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub
Willy Vanhaelen    28 Feb 2014, 13:50
That's right, the Excel build in Data Form doesn't support the change event. But this isn't a surprise since it is a remnant of older versions and excel keeps it only for backward compatibility.
Mar33    26 Feb 2014, 09:43
Hi, I tried the macro and it works great. The only problem I am having is that when I put the data into excels built-in form box and hit enter it does not reconize this as a change event even though the cell does have the new data in it. Any way to have it work while using forms instead of putting the data directly into the cell.

Thanks
Willy Vanhaelen    19 Feb 2014, 10:51
In fact your macro works too perfect. Whenever you enter anything anywhere in your entire worksheet, this macro is fired and your range "B2" gets sorted. The code of your macro is OK but you must change its name: i.e. Sub SortB() and assign it a shortcut key (End is not allowed in Excel, you can only use Ctrl+letter or Ctrl+Shift+letter) or you can create a button in the QAT of it.
michael    18 Feb 2014, 07:00
Hi there i tried the following macro which works just perfect:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Range("B1").Sort Key1:=Range("B2"), _
      Order1:=xlDescending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub

But could i make it work after i press a specific button? for example i start entering my data and when i am finished to press "End" button to activate the sorting?
thank you
eric    02 Dec 2013, 15:44
Yes. Everything came back to normal. Your assumption was totally correct.

I think everybody should pay attention to this tiny macro which can save you from immense frustration.

All respects, Willy, for your excellent Excel knowledge. This site is the best out of many other very good Excel sites.

Thank you for sharing with us your expertise!
Willy Vanhaelen    02 Dec 2013, 10:59
Eric, it seems to me that for some reason in your laptop the EnableEvents is perhaps set to False by an add-in or some workbook that is loaded on startup.

This macro should fix it for the current session:

    Sub EventsEnable()
        Application.EnableEvents = True
    End Sub

If it does, my assumption is correct and you'll have to find the offender.


eric    01 Dec 2013, 13:00
Additional info: After one laptop restart the code works in newly created files, but if I paste it in existing ones, doesn't.
eric    30 Nov 2013, 16:44
I tried to apply your macro and there is something which drives me crazy.

On desktop it worked. On laptop doesn't work.

Even I use the same file transferred between pc's I got the same behavior.

I use on both machines the same version of Excel (2007), same antivirus.

Moreover, I created in the same file other simple macros on laptop. They work. Other files with rich VBA code, created on desktop and transferred on laptop, they work.

Seems to be a tiny setting forgotten somewhere, but what?

For two days I try restlessly to identify the problem. Can you give me an idea what i'm missing?

Thank you.
cameron    29 Nov 2013, 21:42
Never mind. It wasn't too hard to figure out.
cameron    29 Nov 2013, 20:43
Thanks for the post Willy. What do I need to change in order to be able to do the sorting on Column "C"?
Willy Vanhaelen    17 Nov 2013, 09:31
I use this sorting technique for many years now and I solved the disconcerting effect that your entry seems to disappear and you have to search for it to continue. Here it is (for column A):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Or Target.Columns.Count > 1 Then Exit Sub
    Dim tmp As Variant
    tmp = Cells(Target.Row, 2).Formula 'save contents
    On Error GoTo Enable_Events
    Application.EnableEvents = False
    Cells(Target.Row, 2) = "#"
    Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    Cells(Application.Match("#", Columns(2), 0), Selection.Column).Select
    Cells(Selection.Row, 2) = tmp 'restore contents
Enable_Events:
    Application.EnableEvents = True
End Sub

If there is any chance that there could be a record with a single # in the second column, simply replace the # in the macro with one or more characters that are unique.
Willy Vanhaelen    16 Nov 2013, 13:33
Steven, in Excel you can enter the date and time in one cell. You then have to format it accordingly. That solves your problem.
steven    14 Nov 2013, 08:38
i have a document that is used by my mother in law to hold data on children and clients she child minds for
it has 10 colums a-j and is about 50 rows deep
my problem is that i need it to auto sort by date and time these details are in colums d (date) and e (time)
i have tried making up a macro to do it but it didnt work correctly if anyone could help i would be very greatful.

thanks
steven
will    31 Oct 2013, 21:45
Hi! This tips is awesome.

I would like to use this but make it appear on different sheet, would this be possible?

Please help. thanks in advance.

Will
Dragoniel    26 Sep 2013, 06:56
Works flawlessly, thank you.
Miguel    12 Mar 2013, 12:37
This is really awasome, i am using to create time line for my project, and every time i add something even if the date is older or newer this macro put everithing in order just pressing enter Thanks.
Dappy    05 Mar 2013, 01:43
Topic: Automatically Center Cells using the center align on the tool bar.

I stumbled on your "Automatically Sorting as You Enter Information." I am looking for a VBA code to automatically center (align cells to the center) Pivot Table cells (for example range B3:B30,C3:C30).

Thanks in advance for your assistance.
Roy Prockter    17 Apr 2012, 13:22
This is interesting, I'd like to follow the thread

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.