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 |
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 |
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 |
All times are GMT +1. The time now is 08:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com