Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit to number of rows copied in AdvancedFilter
AdvancedFilter seems to work well when number of rows to be copied is
less than 1000, but when more than 1000 rows are to be copied all of the rows in the database are copied. Is there a way to get more than 1000 rows at at time? My application takes rows from one workbook to another after the user has manually filtered the database. The column headers order may be different in the source workbook than the destination workbook. I use a unique key in the criteria range. The only way I can think of doing this is to do multiple AdvancedFilter copies and offset the CopyToRange (with headers) and then delete the headers. Any suggestions? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit to number of rows copied in AdvancedFilter
I just copied 10522 rows (a subset of 21000 rows) to another workbook with
headers in a different order and had no problem. Exactly what was expected was copied. Perhaps you don't have the correct criteria or your ranges are not properly defined. Are you sure you mean Advanced Filter. Autofilter only displays 1000 unique choices in the dropdown as an example, but it can filter all entries. -- Regards, Tom Ogilvy "dbKemp" wrote in message ups.com... AdvancedFilter seems to work well when number of rows to be copied is less than 1000, but when more than 1000 rows are to be copied all of the rows in the database are copied. Is there a way to get more than 1000 rows at at time? My application takes rows from one workbook to another after the user has manually filtered the database. The column headers order may be different in the source workbook than the destination workbook. I use a unique key in the criteria range. The only way I can think of doing this is to do multiple AdvancedFilter copies and offset the CopyToRange (with headers) and then delete the headers. Any suggestions? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit to number of rows copied in AdvancedFilter
Thanks for reply.
I mean AdvancedFilter.... I am doing it programmatically. For me it works all the way up to 1000, but as soon as there are more than 1000, all the rows get copies. The database has 38+ colums and some of the cells contain large amounts of text (up to the max Excel allows in a cell) I am sure that the criteria and named ranges are correct... I stopped the process and checked. I am hesitant to supply the code because there are so many constants & variables that are not intuitively obvious. I have tried this in O2000 & O2003 with same result.... On Oct 8, 11:01 am, "Tom Ogilvy" wrote: I just copied 10522 rows (a subset of 21000 rows) to another workbook with headers in a different order and had no problem. Exactly what was expected was copied. Perhaps you don't have the correct criteria or your ranges are not properly defined. Are you sure you mean Advanced Filter. Autofilter only displays 1000 unique choices in the dropdown as an example, but it can filter all entries. -- Regards, Tom Ogilvy "dbKemp" wrote in oglegroups.com... AdvancedFilter seems to work well when number of rows to be copied is less than 1000, but when more than 1000 rows are to be copied all of the rows in the database are copied. Is there a way to get more than 1000 rows at at time? My application takes rows from one workbook to another after the user has manually filtered the database. The column headers order may be different in the source workbook than the destination workbook. I use a unique key in the criteria range. The only way I can think of doing this is to do multiple AdvancedFilter copies and offset the CopyToRange (with headers) and then delete the headers. Any suggestions? Thanks.- Hide quoted text -- Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit to number of rows copied in AdvancedFilter
I did 50 columns, 21001 rows with 10521 rows copied using a macro. 4 of the
columns had 255 characters in each cell and all worked well. If you have cells with 32000 characters, then maybe you need to go to a database program. -- Regards, Tom Ogilvy "dbKemp" wrote in message oups.com... Thanks for reply. I mean AdvancedFilter.... I am doing it programmatically. For me it works all the way up to 1000, but as soon as there are more than 1000, all the rows get copies. The database has 38+ colums and some of the cells contain large amounts of text (up to the max Excel allows in a cell) I am sure that the criteria and named ranges are correct... I stopped the process and checked. I am hesitant to supply the code because there are so many constants & variables that are not intuitively obvious. I have tried this in O2000 & O2003 with same result.... On Oct 8, 11:01 am, "Tom Ogilvy" wrote: I just copied 10522 rows (a subset of 21000 rows) to another workbook with headers in a different order and had no problem. Exactly what was expected was copied. Perhaps you don't have the correct criteria or your ranges are not properly defined. Are you sure you mean Advanced Filter. Autofilter only displays 1000 unique choices in the dropdown as an example, but it can filter all entries. -- Regards, Tom Ogilvy "dbKemp" wrote in oglegroups.com... AdvancedFilter seems to work well when number of rows to be copied is less than 1000, but when more than 1000 rows are to be copied all of the rows in the database are copied. Is there a way to get more than 1000 rows at at time? My application takes rows from one workbook to another after the user has manually filtered the database. The column headers order may be different in the source workbook than the destination workbook. I use a unique key in the criteria range. The only way I can think of doing this is to do multiple AdvancedFilter copies and offset the CopyToRange (with headers) and then delete the headers. Any suggestions? Thanks.- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit to number of rows copied in AdvancedFilter
Tom,
Thanks much for your attention to this. I agree with the 32000 character comment, however.... One point that may not have been clear: When I refer to more than 1000 rows to be copied that also means that there are more than 1000 rows in the criteria range. Is there a limit to the number of rows in the criteria range? ps. sorry for delay in response... I was out digging holes for a new deck. On Oct 8, 1:18 pm, "Tom Ogilvy" wrote: I did 50 columns, 21001 rows with 10521 rows copied using a macro. 4 of the columns had 255 characters in each cell and all worked well. If you have cells with 32000 characters, then maybe you need to go to a database program. -- Regards, Tom Ogilvy "dbKemp" wrote in ooglegroups.com... Thanks for reply. I mean AdvancedFilter.... I am doing it programmatically. For me it works all the way up to 1000, but as soon as there are more than 1000, all the rows get copies. The database has 38+ colums and some of the cells contain large amounts of text (up to the max Excel allows in a cell) I am sure that the criteria and named ranges are correct... I stopped the process and checked. I am hesitant to supply the code because there are so many constants & variables that are not intuitively obvious. I have tried this in O2000 & O2003 with same result.... On Oct 8, 11:01 am, "Tom Ogilvy" wrote: I just copied 10522 rows (a subset of 21000 rows) to another workbook with headers in a different order and had no problem. Exactly what was expected was copied. Perhaps you don't have the correct criteria or your ranges are not properly defined. Are you sure you mean Advanced Filter. Autofilter only displays 1000 unique choices in the dropdown as an example, but it can filter all entries. -- Regards, Tom Ogilvy "dbKemp" wrote in oglegroups.com... AdvancedFilter seems to work well when number of rows to be copied is less than 1000, but when more than 1000 rows are to be copied all of the rows in the database are copied. Is there a way to get more than 1000 rows at at time? My application takes rows from one workbook to another after the user has manually filtered the database. The column headers order may be different in the source workbook than the destination workbook. I use a unique key in the criteria range. The only way I can think of doing this is to do multiple AdvancedFilter copies and offset the CopyToRange (with headers) and then delete the headers. Any suggestions? Thanks.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit to number of rows copied in AdvancedFilter
I guess that is what you meant by Unique key - although it certainly wasn't
clear that this is the situation you are describing. Nonetheless, I had no problem retrieving 4000 unqiue keys from the before described database. (4000 rows in the criteria range plus the header row, 4000 rows pulled from the database with the query). Perhaps you are not aware that a criteria of just ABC would pull ABC ABCD ABCDEF ABCDEFGH etc. -- Regards, Tom Ogilvy "dbKemp" wrote in message oups.com... Tom, Thanks much for your attention to this. I agree with the 32000 character comment, however.... One point that may not have been clear: When I refer to more than 1000 rows to be copied that also means that there are more than 1000 rows in the criteria range. Is there a limit to the number of rows in the criteria range? ps. sorry for delay in response... I was out digging holes for a new deck. On Oct 8, 1:18 pm, "Tom Ogilvy" wrote: I did 50 columns, 21001 rows with 10521 rows copied using a macro. 4 of the columns had 255 characters in each cell and all worked well. If you have cells with 32000 characters, then maybe you need to go to a database program. -- Regards, Tom Ogilvy "dbKemp" wrote in ooglegroups.com... Thanks for reply. I mean AdvancedFilter.... I am doing it programmatically. For me it works all the way up to 1000, but as soon as there are more than 1000, all the rows get copies. The database has 38+ colums and some of the cells contain large amounts of text (up to the max Excel allows in a cell) I am sure that the criteria and named ranges are correct... I stopped the process and checked. I am hesitant to supply the code because there are so many constants & variables that are not intuitively obvious. I have tried this in O2000 & O2003 with same result.... On Oct 8, 11:01 am, "Tom Ogilvy" wrote: I just copied 10522 rows (a subset of 21000 rows) to another workbook with headers in a different order and had no problem. Exactly what was expected was copied. Perhaps you don't have the correct criteria or your ranges are not properly defined. Are you sure you mean Advanced Filter. Autofilter only displays 1000 unique choices in the dropdown as an example, but it can filter all entries. -- Regards, Tom Ogilvy "dbKemp" wrote in oglegroups.com... AdvancedFilter seems to work well when number of rows to be copied is less than 1000, but when more than 1000 rows are to be copied all of the rows in the database are copied. Is there a way to get more than 1000 rows at at time? My application takes rows from one workbook to another after the user has manually filtered the database. The column headers order may be different in the source workbook than the destination workbook. I use a unique key in the criteria range. The only way I can think of doing this is to do multiple AdvancedFilter copies and offset the CopyToRange (with headers) and then delete the headers. Any suggestions? Thanks.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I limit the number of rows and colums | Excel Discussion (Misc queries) | |||
is there a limit to the number of rows a worksheet can have? | Excel Discussion (Misc queries) | |||
Limit the number of rows for a filter | Excel Discussion (Misc queries) | |||
How to limit number of rows Excel? | Excel Discussion (Misc queries) | |||
Is there a limit to the number of rows that can be filtered | Excel Worksheet Functions |