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.

 

[CG]

CG used his keyboard to write :












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.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Garry,

When I opened it this time it worked perfectly. I took your
suggestion anyway and hope I never see it again. I did change
Header:=xlGuess to Header:=xlNo

Thanks

 

[GS]

CG wrote on 8/15/2011 :

Garry,

When I opened it this time it worked perfectly. I took your
suggestion anyway and hope I never see it again. I did change
Header:=xlGuess to Header:=xlNo

Thanks

Thanks for the feedback! Glad you got it working...