VBA to Auto Sort Colums

[TheGreenApple]

Hi there,

I want to sort values in descending order of their amount of occurrences in multiple tables say 123 occurs 5 times and 120 occures 3 times, then I need 123 to be listed ahead of 120 along with the times they occur. With vlookup I get the amount of occurrences and with the following code this has worked just fine:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.EnableEvents = True
End If
End Sub

However, I need to repeat this for Columns D:E; G:H etc. (7 times in total) as I have 7 sets of date which must not be mixed. This is where I'm struggling. How can I do this multiple times independent from one another in the same worksheet please?

Cheers

 

[Logit]

.
You can add additional columns to this macro. It auto-sorts as you type :

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


Range("B18:B40").Sort key1:=Range("B18:B40"), _
   order1:=xlAscending, Header:=xlNo
   
Range("C1:C40").Sort key1:=Range("C1:C40"), _
   order1:=xlDescending, Header:=xlNo


End Sub

 

[TheGreenApple]

Thanks a lot. I've made it work with this eventually:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 1 Then
    Application.EnableEvents = False
    Range("A2:B23").Sort Key1:=Range("A2"), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Application.EnableEvents = True
End If
If Target.Column = 4 Then
    Application.EnableEvents = False
    Range("D2:E23").Sort Key1:=Range("D2"), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Application.EnableEvents = True
End If
End Sub

 

[TheGreenApple]

There's one more question that has arisen throughout the process:

I was under the impression, that this code would sort my data automatically, however, now after having used it plenty, I always need to set a manual trigger like hitting enter which can be very bothersome in my case. Would anyone know how to make it rank my data automatically without needing to hit hinter every time my data receives new input or what may have lead to this issue?