Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Dynamic Named Range Sort Problem
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(ColNameLi st)) 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??? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Dynamic Named Range Sort Problem
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(ColNameLi st)) 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Dynamic Named Range Sort Problem
On Aug 15, 2:02*pm, GS wrote:
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(ColNameLi st)) 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. -- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Dynamic Named Range Sort Problem
CG wrote on 8/15/2011 :
On Aug 15, 2:02*pm, GS wrote: 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(ColNameLi st)) 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. -- 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 Thanks for the feedback! Glad you got it working... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links to Dynamic Named Range = Problem | Excel Worksheet Functions | |||
Problem with named range for a large spreadsheet in Excel 2007 | Excel Discussion (Misc queries) | |||
Creating a macro to sort a complex dynamic named reference | Excel Programming | |||
Problem with Dynamic Named Range | Excel Worksheet Functions | |||
Vlookup in large named range | Excel Worksheet Functions |