Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing the range of a table sort
Hi All
I have a simpple player league table as shown below: Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 I use the Sort method in the worksheet_change method to sort the table as follows: .Columns("B:C").Sort Key1:=Range("C2"), _ Order1:=xlDescending, _ Key2:=Range("B2"), _ Order2:=xlAscending, _ Header:=xlYes This all works fine, but if I insert say a couple of new rows before the table so that the Player and Points cells are at C4 and B4 respectivelly, even if I change the Range parameters in the sort call to C4 and B4 the sort fails - seems to think I'm still using the original locations(C2 and B2) - I've saved and reopend the worksheet but still see the same behaviour. Any ideas what I've done wrong ? TIA RH |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing the range of a table sort
Red, when you change the spreadsheet, the VBA code does not change, you
should go to the code and change manualy the order keys hope it helps and thanks for the feedback Regards from Brazil Marcelo "RedHook" escreveu: Hi All I have a simpple player league table as shown below: Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 I use the Sort method in the worksheet_change method to sort the table as follows: .Columns("B:C").Sort Key1:=Range("C2"), _ Order1:=xlDescending, _ Key2:=Range("B2"), _ Order2:=xlAscending, _ Header:=xlYes This all works fine, but if I insert say a couple of new rows before the table so that the Player and Points cells are at C4 and B4 respectivelly, even if I change the Range parameters in the sort call to C4 and B4 the sort fails - seems to think I'm still using the original locations(C2 and B2) - I've saved and reopend the worksheet but still see the same behaviour. Any ideas what I've done wrong ? TIA RH |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing the range of a table sort
Hi Marcelo
I did change the VBA code to reflect what I thought should be the new Range values C4 and B4(i.e. the new cell positions of the table column headers; Player and Points), but it makes no difference - I'm obviously missing something basic here but I can't see what ! Regards RH Marcelo wrote: Red, when you change the spreadsheet, the VBA code does not change, you should go to the code and change manualy the order keys hope it helps and thanks for the feedback Regards from Brazil Marcelo "RedHook" escreveu: Hi All I have a simpple player league table as shown below: Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 I use the Sort method in the worksheet_change method to sort the table as follows: .Columns("B:C").Sort Key1:=Range("C2"), _ Order1:=xlDescending, _ Key2:=Range("B2"), _ Order2:=xlAscending, _ Header:=xlYes This all works fine, but if I insert say a couple of new rows before the table so that the Player and Points cells are at C4 and B4 respectivelly, even if I change the Range parameters in the sort call to C4 and B4 the sort fails - seems to think I'm still using the original locations(C2 and B2) - I've saved and reopend the worksheet but still see the same behaviour. Any ideas what I've done wrong ? TIA RH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set the pivot table to auto-update the data range? | Excel Discussion (Misc queries) | |||
Crate group of date, with Dynamic Range in pivot table not working | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linking table in Excel to word | Links and Linking in Excel | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |