Limit Advanced Filter results

Good afternoon all,

Is there a way to limit the number of rows to be used (pasted) by the advanced filter?

I have a macro that runs the advanced filter and I want to be given only 3 rows of results, no matter how many valid data is found in the source. This is because I have other data below, on the same sheet, that I don't want to be overwritten no matter what. I have manually selected that target range, but when there are more than 3 valid results I get the message "The destination range is not large enough to accommodate all copied rows. Data below the destination range will be lost. Continue copying anyway?"

I'm using the code below and thought the EnableEvents = False code would stop the warning message, but it doesn't.

Please Login or Register  to view this content.

Is there a way?

Thank you all in advance.

Hi

I would move the first row of the other data down to row 100 or 1000 or 10000 and the problem goes away
There are 1,048,576 rows available - so might as well use them.

Or you could
- copy the filtered data to a different range first
- then copy/paste only the first 3 rows
- then clear contents of the CopyToRange

Hi all,

Thank you for the quick answers. Specially to @kev_ and @TMS for the simple but nice solution!
I was looking for the complicated version for no reason

Cheers!

It's all too easy to complicate simple things with Excel - Excel's cleverness encourages it!
Thanks for the rep