+ Reply to Thread
Results 1 to 9 of 9

Limit Advanced Filter results

  1. #1
    Registered User
    Join Date
    07-09-2014
    Location
    Lisbon
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 1708 (build 8431.2153)
    Posts
    62

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

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Limit Advanced Filter results

    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.

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Limit Advanced Filter results

    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

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Limit Advanced Filter results

    Hi no_Fate

    Not tested...you could try

    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,815

    Re: Limit Advanced Filter results

    @jaslake: if you use DisplayAlerts = False, it WILL overwrite the following data. I don't think that is what is wanted.

    I'd be inclined to output the filtered data in spare columns to the right rather than below current data.

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Limit Advanced Filter results

    @TMS

    You're more than likely correct...as I indicated, not tested, as the OP has not attached a File.

  7. #7
    Registered User
    Join Date
    07-09-2014
    Location
    Lisbon
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 1708 (build 8431.2153)
    Posts
    62

    Re: Limit Advanced Filter results

    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!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,815

    Re: Limit Advanced Filter results

    You're welcome. Thanks for the rep.

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Limit Advanced Filter results

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Different results for Auto Filter and Advanced Filter
    By me20161130 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-01-2016, 09:02 AM
  2. [SOLVED] Advanced filter not equal (<>) results
    By whocares4 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2015, 01:15 PM
  3. [SOLVED] Advanced Filter Macros - Inconsistant Results
    By TheScott in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-03-2015, 11:04 AM
  4. [SOLVED] Advanced Filter Help - No Results in List Range
    By jcroucier in forum Excel General
    Replies: 12
    Last Post: 09-29-2014, 08:56 AM
  5. [SOLVED] Advanced Filter results don't match auto filter
    By WaterWings in forum Excel General
    Replies: 1
    Last Post: 09-13-2012, 05:37 PM
  6. Results of List of Data with Advanced Filter
    By vtphilk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2008, 06:21 PM
  7. refresh advanced filter results
    By eagle in forum Excel General
    Replies: 2
    Last Post: 11-18-2005, 05:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1