Excel VBA: Auto sort when new row is inserted

Question

I have a table with data from A5:S and would like to sort by a column with "segment" in headline every time a line is inserted.

I have made a numeric column to the left of my string column "segment" which matches my "ranking", the only issue is that it doesn't sort the rows automatically.

I have tried this VBA, but nothing happen:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then

        Dim lastRow As Long
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Range("A5:S" & lastRow).Sort key1:=Range("A5:A" & lastRow), order1:=xlAscending, Header:=xlGuess

    End If

End Sub

score 0 · Answer 1 · edited Dec 03 '18 at 17:51

0

What about if you change:

if target.column = 2 then

for

if activecell.column = 2 then

edited Dec 03 '18 at 17:51

cybernetic.nomad

6,100
3
18
31

answered Dec 03 '18 at 15:03

Juan Joya

120
5

Please take the time to read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Dec 03 '18 at 17:51

score 0 · Accepted Answer · answered Dec 03 '18 at 15:58

If you keep a count of the #of rows in Column A, then when you insert a row, the worksheet_change event can run when the rows increase. Possible adding enableevents to false would be a good idea so the change_evnt does not kick in when sorting

Dim LstRw As Long

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rws As Long
        Rws = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row

        If Rws > LstRw Then
            Application.EnableEvents = False
            MsgBox "Run your code here!"
            Application.EnableEvents = True
        End If

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        LstRw = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
    End Sub

Excel VBA: Auto sort when new row is inserted

2 Answers2