Table auto sort

[nparsons75]

I have a table that consists of approximately 8 rows. Each row has data in order of priority. example, priority 1 at the top, 8 at the bottom. The priorities do change. For example, what is priority 3 may become priority 1.

I need to be able to quickly change the priority. Ideally by changing the priority number all the rows will reorganise automatically.

Currently I have to cut, copy and paste which becomes messy.

Also to bear in mind the spreadsheet is shared and used by many people.

Hopefully, someone can assist which will be greatly appreciated, thank you.

 

[ParamRay]

just an issue i have come across. If i delete the top row as we do not need it anymore that leaves a blank row, the module will obviously no longer work. is there a workaround for this do you know of?

Replace the code I posted earlier with the following code. This way, it doesn't matter whether or not you keep the row of headers. It will always sort column A in ascending order.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim SRng As Range
    Set SRng = Me.Range("A1").CurrentRegion    

    If Not Intersect(SRng, Target) Is Nothing Then
        
        SRng.Sort _
            Key1:=Me.Range("A1"), _
            Order1:=xlAscending, _
            Header:=xlGuess
    End If
    
End Sub