Large Dynamic Named Range Sort Problem

C

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???
 
G

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top