How Can I Sort the Dates Automatically Not Messing Up The Format

[Ratan868]

A Screenshot of the worksheet is attached below. Help needed please as I'm new to using codes in VBA.

https://1drv.ms/u/s!AuJosJM5re-thDoADN_0odaoTIQH

 

[Joe4]

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

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rw As Long
    
'   Only run if a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if update not in columns C, D, or E
    If Intersect(Target, Range("C:E")) Is Nothing Then Exit Sub
    
'   Exit if update above column 5
    If Target.Row <= 5 Then Exit Sub
    
'   Check to see if values in columns C, D, and E
    rw = Target.Row
    If Len(Cells(rw, "C")) > 0 And Cells(rw, "D") > 0 And Cells(rw, "E") > 0 Then
        Application.EnableEvents = False
        Call MySort
        Application.EnableEvents = True
    End If
    
End Sub



Sub MySort()

    Dim lastRow As Long
    
'   Find lastRow with data in column C
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row

'   Sort results by columns D then E
    Range("C5:Q" & lastRow).Sort _
        key1:=Range("D5"), order1:=xlAscending, _
        key2:=Range("E5"), order2:=xlAscending, Header:=xlYes

End Sub

Now, try entering data. Once columns C, D, and E are all populated for a new row of data, it will automatically sort.

 

[Ratan868]

Thank you so much Joe4, it's working perfectly, however, can it be amended so that it would sort after the data in G, I and K are populated as well (inclusive of C, D and E)?

 

[Joe4]

Just add more "And" clauses to this line:

If Len(Cells(rw, "C")) > 0 And Cells(rw, "D") > 0 And Cells(rw, "E") > 0 Then

Note the different structures.

For text entries, I used the LEN function to check to see if there is any length string in the cell, i.e.

Len(Cells(rw, "C")) > 0

For date/numbers, I simply checks to see if there is a value greater than 0:

Cells(rw, "D") > 0

Note that the "C" and "D" represent the column numbers.
Armed with that new knowledge, you should be able to expand the original line of code I posted about, adding additional AND statement for each additional condition (column) that you want to check.

 

[Ratan868]

This is the code I used but it's not sorting now. Also what code to use for lines that have both Text and Numbers e.g SD5 or MR12


Private Sub Worksheet_Change(ByVal Target As Range)


Dim rng As Range
Dim rw As Long

' Only run if a single cell is updated
If Target.CountLarge > 1 Then Exit Sub

' Exit if update not in columns C, D, or E
If Intersect(Target, Range("C:E")) Is Nothing Then Exit Sub

' Exit if update above column 5
If Target.Row <= 5 Then Exit Sub

' Check to see if values in columns C, D, and E
rw = Target.Row
If Len(Cells(rw, "C")) > 0 And Cells(rw, "D") > 0 And Cells(rw, "E") > 0 And Len(Cells(rw, "G")) > 0 And Cells(rw, "I") > 0 Then
Application.EnableEvents = False
Call MySort
Application.EnableEvents = True
End If

End Sub






Sub MySort()


Dim lastRow As Long

' Find lastRow with data in column C
lastRow = Cells(Rows.Count, "C").End(xlUp).Row


' Sort results by columns D then E
Range("C5:Q" & lastRow).Sort _
key1:=Range("D5"), order1:=xlAscending, _
key2:=Range("E5"), order2:=xlAscending, Header:=xlYes


End Sub

 

[Joe4]

Also what code to use for lines that have both Text and Numbers e.g SD5 or MR12

That is text. Note that text can contain numbers. As matter as fact, text can be all numbers (if you format the the cell as Text before entry). Things like Zip Codes and Social Security Numbers, which may contain leading zeroes fall into this category.

If you are unsure, you can actually use the LEN function check on ALL entries, as numeric entries have a length too.

Note, in your example, column G was actually blank for EVERY entry. So make sure that you are, in fact, referencing the correct columns in your formula.

 

[Ratan868]

Ok I understand. So if there isn't any information in the other columns, it would not sort?

Also, do I need to add the other columns in the other lines e.g ' Check to see if values in columns C, D, and E and Exit if update not in columns C, D, or E or just e.g[ If Len(Cells(rw, "C")) > 0 And Cells(rw, "D") > 0 And Cells(rw, "E") > 0 Then]?

 

[Joe4]

All we are trying to do is determine at what point in time the sort should happen. We could have it sort every time any single cell is updated, but that would get rather annoying as you enter a new record and it keeps sorting after every column entry for that one record. So just choose whatever columns need to be completed for a record before it sorts. Note that whatever columns you pick for that IF statement, ALL of them need be populated before the sort will happen.

Note that we will probably need to update this line too, to reflect the new columns we are adding:

'   Exit if update not in columns C, D, or E
    If Intersect(Target, Range("C:E")) Is Nothing Then Exit Sub