Sort descending in column "N"

[gemini528]

Dear Excel experts,

I need a slight revision on the code below to sort (descending) starting in column “N7” up to the last row with data.

Any help would be appreciated.

Thank you.

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim Rng         As Range
Dim Dn          As Range
Dim Dic         As Object
Dim Temp        As String
Dim oCols       As String
Dim R           As Range
Dim C           As Long
Dim ws As Worksheet

Set ws = Sheets("PubQ1")

   If Not Application.Intersect(Target, Range("C3")) Is Nothing Then
       Target(1).Value = UCase(Target(1).Value)
   End If
    
 With Sheets("dBase")
    .Unprotect Password:="."
    .AutoFilterMode = False
    .Range("J7:V7").AutoFilter
    Set Rng = .Range(.Range("J8"), .Range("J" & Rows.Count).End(xlUp))
 End With

Set Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
    
C = 7

For Each Dn In Rng
    If Not Dic.exists(Dn.Value) Then
        Dic.Add Dn.Value, Dn
    Else
        Set Dic.Item(Dn.Value) = Union(Dic.Item(Dn.Value), Dn)
    End If
Next

C = 7
    With Sheets("PubQ1")
       .Unprotect Password:="."
       .AutoFilterMode = False
       .Range(.Range("N6"), .Range("N" & Rows.Count).End(xlUp)).Resize(, 20).ClearContents
       .Range("N7").Resize(, 20).Value = Array("KEY DATE", "KEYED BY", "PUB/LOC/COMMENT", "PUB/LOC", "REC", "ISS", "ADJ QTY", "PICK QTY", "TR DATE", "PUB NO.", "DESCRIPTION", "TR DATE", "TR TYPE", "TR QTY", "PALLET", "ROW", "POS", "LEVEL", "UNI LOC", "COMMENT")
        If Dic.exists(.Range("C3").Value) Then
            For Each R In Dic.Item(.Range("C3").Value)
              C = C + 1
                .Range("N" & C).Resize(, 20).Value = R.Offset(, -9).Resize(, 20).Value
              Next R
      End If
       .Range("A7:F7").AutoFilter
       .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True, _
        Password:="."
End With

 If Not Sheets("PubQ1").AutoFilterMode Then
  ws.Unprotect Password:="."
  ws.Range("A7:F7").AutoFilter
  ws.Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True, _
        Password:="."
 End If
 
 Sheets("dBase").Protect Password:=".", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
  
 With Sheets("dBase")
    .Protect _
      Contents:=True, _
      AllowFiltering:=True, _
      UserInterfaceOnly:=True, _
      Password:="."
 End With
  
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub