Auto Sorting Macro

[dtrusnik06]

I am using the following macro to auto sort entire rows by dates entered into column "N". The first row in the data that needs to be sorted is "4". I will need this to auto sort any rows that are added into worksheet and dates entered. It was working fine but it just started not auto sorting entire row, it only auto sorts the dates in column "N". Is there any fix that will solve this for me?

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

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("N:N")) Is Nothing Then
Range("A4:N[COLOR=#ff0000]20000[/COLOR]").Sort Key1:=Range("N5"), _
Order1:=xlAscending, header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Where the number in red is large enough to cover all your data

 

[Joe4]

That can happen if you have any blank columns in your row.

We can find dynamically find the last row in column N with data, and tell it to sort the entire rows, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long

    On Error Resume Next
    
    If Not Intersect(Target, Range("N:N")) Is Nothing Then
'   Find last row in column N
    lr = Cells(Rows.Count, "N").End(xlUp).Row
'   Sort entire rows, starting on row 4
    Rows("4:" & lr).Sort Key1:=Range("N5"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    End If
    
End Sub