ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   Changing the range of a table sort (https://www.excelbanter.com/new-users-excel/91264-changing-range-table-sort.html)

RedHook

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


Marcelo

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



RedHook

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