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]

.
.

Place the following code in the code module for your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim SRng As Range
    Dim KRng As Range
    
    Set SRng = Me.Range("A1").CurrentRegion 'sort range
    
    If Not Intersect(SRng, Target) Is Nothing Then
        
        On Error GoTo Handler
        Set KRng = SRng.Rows(1).Find(What:="priority")  'key range
        On Error GoTo 0
        
        SRng.Sort _
            Key1:=KRng, _
            Order1:=xlAscending, _
            Header:=xlYes
    End If
    
Handler:

End Sub

If your data does not start in cell A1 then you'll need to change this line first:

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

 

[nparsons75]

Hi, will this sort all rows in the table and if so, what will it sort by? Thank you very much.

 

[ParamRay]

Hi, will this sort all rows in the table and if so, what will it sort by? Thank you very much.

Yes, it will sort the entire contiguous region around cell A1 (i.e. all cells surrounding that cell until there's an empty row or column).

It looks for a cell containing the text "priority" in the top row and then sorts that column in ascending order...

 

[nparsons75]

Hi, not sure how this works, I have done what you suggest (I hope) but cant get anything to work. Hope you can help.

 

[nparsons75]

example of my sheet.

I have 6 rows and 4 columns.

col 1 = priority
col 2 = item number
col 3 = price
col 4 = price

row 1 = data
row 2 = data
etc
etc.

I need the row data to be in ascending order based on the priority number in column 1.

So if I change the number from 3 to 1 that row will jump to the top.

Hope that explains it better, thanks.

 

[nparsons75]

I have added a link to a demo.

I need to be able to change the number in the priority column and then the data will ascend (all rows included)

https://www.dropbox.com/s/0d7mw2qd1mhr3uj/EXAMPLE.xlsm?dl=0

 

[ParamRay]

.
.

The macro I posted earlier will work with your sample worksheet.

Make sure that you place the macro the code module for this worksheet.
(i.e. If your worksheet is named "Sheet1" then the code should go in the code module named "Sheet1".)

Then test it out by changing one of the priorities in column A. The data should sort itself automatically...

 

[nparsons75]

fantastic, working superb, i right clicked the tab and viewed code to enter the macro that way. Thank you very much again.

 

[nparsons75]

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?