Auto Sorting an Area

[default_name]

I have a group of cells (A2:C20) that constantly has new information.

I always have to go into the data/range and sort the values so that they are in order by date.
Since the content of this group of cells is always changing, it is kind of annoying to have to re-sort the list all the time.

Is there a way to only automate this area (A2:C20) of the spreadsheet, so that it automatically sorts by date (column A) as I enter data?

Here's an example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date
[/TD]
[TD]Item
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4/25/19[/TD]
[TD]ABCCC11234[/TD]
[TD]$463.78[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5/19/19[/TD]
[TD]JNLNFMSL2345[/TD]
[TD]$1173.12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5/21/19[/TD]
[TD]KNDFN13205[/TD]
[TD]$24.14[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9/1/19[/TD]
[TD]DASFHK734N[/TD]
[TD]$338.25[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If I enter in a value at the end, like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date
[/TD]
[TD]Item
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4/25/19[/TD]
[TD]ABCCC11234[/TD]
[TD]$463.78[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5/19/19[/TD]
[TD]JNLNFMSL2345[/TD]
[TD]$1173.12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5/21/19[/TD]
[TD]KNDFN13205[/TD]
[TD]$24.14[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9/1/19[/TD]
[TD]DASFHK734N[/TD]
[TD]$338.25[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4/29/19[/TD]
[TD]FDBN2283KJK[/TD]
[TD]$6792.02[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd like it to automatically re-sort itself like so:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date
[/TD]
[TD]Item
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4/25/19[/TD]
[TD]ABCCC11234[/TD]
[TD]$463.78[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4/29/19[/TD]
[TD]FDBN2283KJK[/TD]
[TD]$6792.02[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5/19/19[/TD]
[TD]JNLNFMSL2345[/TD]
[TD]$1173.12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5/21/19[/TD]
[TD]KNDFN13205[/TD]
[TD]$24.14[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9/1/19[/TD]
[TD]DASFHK734N[/TD]
[TD]$338.25
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is this even possible?

Thanks in advance for your help

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rw As Long
    
'   Exit sub if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   See where change occured
    Set rng = Intersect(Target, Range("A2:C20"))
'   If change occurred outside specified range, exit sub
    If rng Is Nothing Then Exit Sub
    
'   Get row that change happened in
    rw = Target.Row
    
'   See if all three columns populated in target row
    If Cells(rw, "A") <> "" And Cells(rw, "B") <> "" And Cells(rw, "C") <> "" Then
'       If so, sort range
        Range("A1:C20").Sort _
            key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
    End If

End Sub

As long as as you entering the data manually one row at a time, this will automatically sort the data once all three columns are populated.

 

[pella88]

Hi default_name,

The easiest way is to add a worksheet_change event in the workbook which would trigger when any data is entered. I would suggest that it triggers once data is entered in the last column (column C in your case), and the code would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Integer
Dim sortRng As Range

Application.ScreenUpdating = False

If Intersect(Target, Range("C:C")) Is Nothing Then
    '
Else
    n = Range("A" & Rows.Count).End(xlUp).Row
    Set sortRng = Range("A1:C" & n)


    sortRng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
 
End If

Application.ScreenUpdating = True

End Sub

Add this code to the module of the worksheet where the sorting should happen. In order to adjust code, you can change "A1:C" range in case you have more than 3 columns, Range("C:C") in case that you want that event is triggered on any other cell, Order1 in case you want descending, Key1 in case you want that sorting is done according to different column.

Br
pella88

 

[default_name]

Thanks guys! This is great!
Your comments in the code are very helpful as well!
Helps me better follow what is going on.

 

[Joe4]

You are welcome.
Glad we were able to help!