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
– Variatus, Commented May 27, 2017 at 6:30

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

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?
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

Collectives™ on Stack Overflow

Sorting Alphabetically using VBA from row 5 onwards

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related