Sort by changing number

[Greg]

I was wondering if there is a way to change the order (sort) of a top 10 list
by changing any number in one column then have the higher numbers move down
one. For example:
Column A is contains numbers 1-10, Column B contains names. If I want to to
change a persons name who is number 8 to number 3, I would just change the
number in column A from 8 to 3 (press enter or tab out of the column) and the
entire row containing the number and name would appear in the number 3 spot
and all numbers and names would move down one. So what was 3 would now be 4,
what was 4 would become 5 all the way to number 10.
Thanks for you respones.

 

[Mike H]

Greg,

Right click the sheet tab, view code and paste this in. Put the number where
you want the sort to start in C1 and columns A&B will be sorted with that
number at the top.

Sub VarySort()
Last = Range("A65536").End(xlUp).Row
With Range(Cells(1, 1), Cells(Last, 2))
.Sort Key1:=Range("A1"), Order1:=xlAscending
End With
For a = 1 To Last
If Left(Cells(a, 1).Value, 1) = Cells(1, 3).Value Then
Cutme = a
Exit For
End If
Next a
Range(Cells(1, 1), Cells(Cutme - 1, 2)).Cut Destination:=Cells(Last + 1,
1)
Last = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(Cutme, 1), Cells(Last, 2)).Cut Destination:=Cells(1, 1)
End Sub


Mike