MeraWindows Forums - Get your Computer Problems Fixed

This is a site run by the community - for the community. At the site, we share and contribute our computer learnings with each other. We discuss about Windows, Office, Mobile, Gaming and other computer related topics. At our forums you can post your queries and our very helpful community members will respond quickly to your queries. We organize lots of competitions from time to time and award prizes to the winners.
You will need to register at the site before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.

Search Home All Forums

Home

Microsoft Offic...

Microsoft Excel

Auto sort as data is entered in the list.

4/24/2012 4:53 PM

Tanmay Bhatt

Joined: 11/11/2009

Posts: 39

Auto sort as data is entered in the list.

Hey all,

I have a sheet in which I have a list of debtors in one column and creditors in another. Now if I have to add a New Name to the list is there any way to auto sort the list as I enter the data.

Thanks in advance for your kind assistance.

4/25/2012 4:52 AM

Accepted Answer

Ashish Mathur

www.ashishmathur.com
Joined: 11/17/2006

Posts: 1680

Re: Auto sort as data is entered in the list.

Hi,

Upload the file and explain the problem very clearly.

For Microsoft Excel trainings in India, please visit http://www.ashishmathur.com/corporate-interventions/

Regards,

Ashish Mathur -

Microsoft Excel MVP
Website
Blog
Follow @ExcelAshish

4/26/2012 9:21 PM

Tanmay Bhatt

Joined: 11/11/2009

Posts: 39

Re: Auto sort as data is entered in the list.

Hi Ashish,

Thank you very much for your reply. I have attached the file for your reference. In the Sheet Name List there are 3 Columns Debtors, Creditors and Expenses. Now if I add a name to debtors list then the whole list should sort in alphabetical order as I add the name, same should be in Creditors and Expenses.

NewExcel (2).xlsx

4/27/2012 5:20 AM

Ashish Mathur

www.ashishmathur.com
Joined: 11/17/2006

Posts: 1680

Re: Auto sort as data is entered in the list.

Hi,

Try this VBA code.

1. Right click in the List tab and select "View code"
2. In the left hand side panel, click on the List worksheet
3. In the right hand side white space, paste the following code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row_no As Integer
If Intersect(Target, Range("A3:C15")) Is Nothing Then
    Exit Sub
Else
    row_no = Target.Row - 1
    If Len(row_no) > 0 Then
        Range(Cells(2, Target.Column), Cells(2, Target.Column).End(xlDown)).Sort Key1:=Range(Cells(2, Target.Column)), _
   Order1:=xlAscending, Header:=yes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Else
        Exit Sub
    End If
End If
End Sub

Now if you add/edit/change any value in range A3:C15 of the List sheet, that specific column will sort in ascending order.

Hope this helps.

For Microsoft Excel trainings in India, please visit http://www.ashishmathur.com/corporate-interventions/

Regards,

Ashish Mathur -

Microsoft Excel MVP
Website
Blog
Follow @ExcelAshish