Large Dynamic Named Range Sort Problem

[CG]

I have a large dynamic named range to sort with the Workbook_Open
event. Using Excel 2003 on XP

db =OFFSET(dbAnchor,0,0,dbRowCount+1,COUNTA(ColNameList))
X is set equal to 1 column of the database that includes the header
row = Range("$O$2:$O$8100").


Range("db").Sort Key1:=x, Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlSortColumns, _
DataOption1:=xlSortNormal

The Error I am getting is Run-time error 1004:

The sort reference is not valid. Make sure that it's within the data
you want to sort, and the first Sort By box isn't the same or blank.
Based on my debuging it has to do with the Range("db") not being
resolved. If I hard code the address of the db it works.

Is there a limitation I am missing? If I delete ~2k rows it works.

If I manually sort it and then run the routine it works fine???

 

[GS]

CG used his keyboard to write :

I have a large dynamic named range to sort with the Workbook_Open
event. Using Excel 2003 on XP

db =OFFSET(dbAnchor,0,0,dbRowCount+1,COUNTA(ColNameList))
X is set equal to 1 column of the database that includes the header
row = Range("$O$2:$O$8100").


Range("db").Sort Key1:=x, Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlSortColumns, _
DataOption1:=xlSortNormal

The Error I am getting is Run-time error 1004:

The sort reference is not valid. Make sure that it's within the data
you want to sort, and the first Sort By box isn't the same or blank.
Based on my debuging it has to do with the Range("db") not being
resolved. If I hard code the address of the db it works.

Is there a limitation I am missing? If I delete ~2k rows it works.

If I manually sort it and then run the routine it works fine???

Normally, a dynamic range would NOT include the header row. This may be
why your throwing an exception. Your explanation of X suggests the
headers are in row2. I suggest you just sort the range without a header
since the first cell of the range is offset 1 row down from the header.