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, May 27 '17 at 06:30

score 1 · Accepted Answer · answered May 27 '17 at 05:53

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

Siddharth Rout, thanks very much for your help! The code does exactly what it says on the tin. I only have a slight problem though. The database I want to use it on has to be protected, to stop accidental changes to some of the calculations. Your code works fine when the worksheet is unprotected, but will not work when the sheet is protected. Is there a workaround where I can have some columns locked but will sort all the rows (A-H) when running your VBA? — Ian Martin, May 27 '17 at 22:53
You can unlock and lock the worksheet in the code itself ;). Unlock the sheet just before the sort and protect it right after the sort — Siddharth Rout, May 28 '17 at 02:51

Sorting Alphabetically using VBA from row 5 onwards

1 Answers1