Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by changing number
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by changing number
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 "Greg" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort an outline without changing the hierarchy | Excel Discussion (Misc queries) | |||
Sort a changing range | Excel Discussion (Misc queries) | |||
Round number to the thousands without changing underlying number | Excel Discussion (Misc queries) | |||
Sort changing formulas | Excel Discussion (Misc queries) | |||
Changing format of number without changing the value | Excel Discussion (Misc queries) |