Can someone please help me with a Excel VBA code

Question

I am trying to create a VBA code to sort all columns based on the priority list. My priority list is an ascending column of numbers 1 through 50 in column A and column B is the list of priorities associated with the numbers. I need it to auto sort when a priority changes. For my test I have 1-9 in column A and a list of automotive companies in column B. If one of the companies changes in priority, I need it sorted to reflect the change. So if priority 5 changes to priority 1. I need it to re-sort it and then adjust everything down. I have a VBA code that re-sorts it when I change the priority number but haven't completed the code to adjust the numbers to reflect the change. Thank you.

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tblT As ListObject 

Dim rngSortCol As Range 

Set tblT = Me.ListObjects("Table1") 

Set rngSortCol = Range("Table1[Priority]") 

If Intersect(Target, rngSortCol) Is Nothing Then Exit Sub 

'Turn off events to keep out of loops 

Application.EnableEvents = False 

With tblT.Sort 

    .SortFields.Clear 

    .SortFields.Add Key:=rngSortCol, Order:=xlAscending 

    .Apply 

End With 

With rngSortCol 

    .FormulaR1C1 = "=ROW()-" & tblT.HeaderRowRange.Row

    .Copy 

    .PasteSpecial Paste:=xlPasteValues 

    Application.CutCopyMode = False 

End With 

Target.Select 

'Turn events back on to get ready for the next change 

Application.EnableEvents = True 

End Sub

Answer 2

Bernie,

I greatly appreciate the help. It is close. When I change a priority it goes 1 number higher than what I want. Is there a adjustment that can be made to make it go to the right priority? For example, if I one to change a priority to priority 1, I have to change the current number to priority 0. Then it shifts everything down correctly. It works, but can I adjust something in the VBA code to make it go to the right priority? Thank you.

Answer 3

=ROW()-1

.FormulaR1C1 = "=ROW()-" & tblT.HeaderRowRange.Row

The above code has reset the priority according to the row index so unecessory to change current number.

Answer 4

After this line:

Application.EnableEvents = False

add the line

Target.Value = Target.Value - 1

Answer 5

"ROW() - 1" only works if the table's data rows start in row 2. If the table is elsewhere, then the numbers don't start with 1.