0

I need to make a simple sheet for a sports event, but I'm new to macro's and VB. Column A will have a person's rank (#1, 2, 3 etc.)
Column B will have their name
Column C will have their score.

I have found a macro to auto-sort when I enter a person's score.
Now, there are two issues I found with it and as I'm new, I can't seem to find the problem.

1: If I enter no name and only a score, it sorts the score anyway. I only want scores with a name to sort. How can I achieve this?
2: The ranking in column A should remain in that order, so we can tell a person his rank (ex. you got in 10th place). Right now, when I enter a score, it sorts it all (so the values in A and B stay with 'their' score. What I want is that the score (col.C) is sorted with the name (col.B) staying with it, but the rank (col.A) stays in the same order as it was (so not tied to the B or C values). How can I achieve this? 

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("B:C")) Is Nothing Then
        Range("C1").Sort Key1:=Range("C2"), _
          Order1:=xlDescending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub
Ralph
  • 9,284
  • 4
  • 32
  • 42
Kailayla
  • 323
  • 5
  • 15
  • It seems to me that you are looking for a custom sort which cannot be created with standard Excel sorting. So, you might want to give this a try: https://stackoverflow.com/questions/37239107/sort-range-without-sorting-it-in-a-spreadsheet/37239386#37239386 – Ralph Jun 24 '16 at 16:21
  • What do you mean by "I only want scores with a name to sort"? Should the best score be #1 regardless of whether it has a name or not? – TheEngineer Jun 24 '16 at 16:57
  • Do you want to achieve results shown in the uploaded snapshot [Don't sort Ist Column](https://dl.dropboxusercontent.com/u/65565270/stackoverflow/Jun_2016/Don%27t%20sort%20Ist%20column%20260616.png). Will any VBA routine which achieve this will do. – skkakkar Jun 26 '16 at 06:16

0 Answers0