Sorting Alphabetically using VBA from row 5 onwards

Question

I am trying to find out how to get a database to automatically sort alphabetically using VBA in column A. Sounds easy, but I have headers in the first 4 rows and want it to sort from line 5 downwards. I have been searching for days to find a code that does this. The nearest I have succeeded is with this code-

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

The problem is when I try changing the line Range("A1").Sort Key1:=Range("A2"), _ to Range("A5").Sort Key1:=Range("A6"), _ when I test it, it still sorts to row 2 and not to row 5 as intended. I know I am missing something, but just cannot see what it is that I am missing!

You are missing the "from" part. The range to be sorted can't be defined by the "to" part alone, and to say that you want to range "to row 5" is a misconception anyway because you want to start with row 5. So, now you see the experts arguing it will be your fault if they give you the wrong answer. Can't blame them, or can you? If you want a useful answer specify the range you want sorted, starting in row 5 and ending where? — Variatus

Siddharth Rout Siddharth Rout · Accepted Answer · 2017-05-27T05:53:07

Please do not misuse OERN (On Error Resume Next). It is like telling the code to Shut up :). Handle the error correctly.

Another interesting read

Is this what you are trying?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long

    On Error GoTo Whoa

    '~> Find the last row in Col A
    lRow = Range("A" & Rows.Count).End(xlUp).Row

    '~~> Check if it is greater than row 4
    If lRow > 4 Then
        Application.EnableEvents = False

        '~~> Check if the change happened in the relevant range
        If Not Intersect(Target, Range("A5:A" & lRow)) Is Nothing Then
            '~~> Sort only the relevant range
            Range("A4:A" & lRow).Sort Key1:=Range("A4"), _
                                      Order1:=xlAscending, _
                                      Header:=xlYes, _
                                      OrderCustom:=1, _
                                      MatchCase:=False, _
                                      Orientation:=xlTopToBottom, _
                                      DataOption1:=xlSortNormal
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Sorting Alphabetically using VBA from row 5 onwards

1 Answers