Excel - Automatically sort by a column if a specific cell changes

Question

I have a report in Excel (2010) which is refreshable. Cell S3 contains a unique 8 digit number which will be different each time the report is refreshed. Rows 1 and 2 are just headers and other info; the actual data is returned on row 3 downwards

What I'd like to be able to do is have the report automatically sort any data from row 3 downwards, A-Z by column F (and of course all the adjacent data along with their respective row, if that makes sense) if the contents of cell S3 is ever changed.

Is this possible in VBA?

Many thanks.

Answer 1

You can use Worksheet_Change event like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Column = 19 And Target.Row = 3 Then 
      Dim lastrow As Long
      lastrow = Cells(Rows.Count, 2).End(xlUp).Row
      Range("A3:F" & lastrow).Sort key1:=Range("F3:F" & lastrow), _
           order1:=xlAscending, Header:=xlNo
    End If 
End Sub

For sorting see here. However:

This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

If you need to check value changes caused by recalculation, you will have to store old value of S3 cell in a variable and compare this old value with a new value. See this answer.