Limit to number of rows copied in AdvancedFilter

[dbKemp]

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.

 

[Tom Ogilvy]

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.

 

[dbKemp]

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....

 

[Tom Ogilvy]

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.

 

[dbKemp]

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.

 

[Tom Ogilvy]

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.