Automatic sort of table into rank order when a new record is added

[tbones]

Hi all, I have been using a table that I input figures onto. What I want to be able to do is to have the rows move in order of rank automatically as new data is inputted.
So say I have 10 columns and say 10 rows and I want to add data to these rows I then want the table to sort in order of largest to smallest of say column 8 .

I have been using a VBA code that will do this if I double click on the header row however I dont want to have to do this and I want it to move the rows in order once the data has been inputted.

Here is the VBA I use so that I can click on the header to sort.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("A1:FE25").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
Range("A1:FE25").Sort Key1:=KeyRange, Header:=xlYes
End If
End Sub

Any help to be able to do the same as the code but without having to double click on the header column to sort would be appreciated.

 

[kevin9999]

Try the following

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("H:H"), Target) Is Nothing Then
    Application.EnableEvents = False
    With Range("A1").CurrentRegion
        .Sort Key1:=Range("H1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
    End If
End Sub

 

[tbones]

Try the following

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("H:H"), Target) Is Nothing Then
    Application.EnableEvents = False
    With Range("A1").CurrentRegion
        .Sort Key1:=Range("H1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
    End If
End Sub

Thank you for this. I have tried this and put in the range I needed however it is coming back as an error highlighted below

Not sure why it it is not working. Would you have an idea at all please?

 

[kevin9999]

Could you post a copy of your sheet using the XL2BB add in? Or alternatively share your file using Dropbox, Google Drive or similar file sharing platform?

 

[kevin9999]

I suspect your dataset might not be contiguous, which means the .CurrentRegion method will not work. Try this instead - although you may need to enable events first (run the first code once only).
This once:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Then replace the code in post #2 with this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("FC:FC"), Target) Is Nothing Then
    Application.EnableEvents = False
    With Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .Sort Key1:=Range("FC1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
    End If
End Sub

 

[tbones]

Try the following

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("H:H"), Target) Is Nothing Then
    Application.EnableEvents = False
    With Range("A1").CurrentRegion
        .Sort Key1:=Range("H1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
    End If
End Sub

I forgot to mention that I am using excel 365

 

[tbones]

Here is the link to the workbook

 

[tbones]

football predictor project 4.xlsm

Microsoft Excel Workbook

1drv.ms

 

[kevin9999]

Thank you for providing the actual file - this changes things totally
Because your column FC changes as a result of a formula, a Worksheet Change event will not work at all - the code needs to be triggered by a worksheet calculate event instead. The drawback is that the sheet will sort whenever a calculation occurs anywhere on the sheet - you can't specify which cells change via a calculation to trigger the code.
Here is the new code:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    With Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .Sort Key1:=Range("FC1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
End Sub

and here is the link to your workbook with the code already added: football predictor project 4.xlsm

 

[tbones]

Oh wow... that works as I wanted it to do so I can only but thank you for your help.
The way I will be populating the table is using different gameweek sheets which I will link to each player. What I do then is to manually input everyones predictions and then once the games have been played i then input the actual scores and it will populate the whole table based on the players predictions. So in essence it works perfectly. Thank you