+ Reply to Thread
Results 1 to 3 of 3

Advanced Filter is Erasing My Other Data!

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    221

    Advanced Filter is Erasing My Other Data!

    I have a range that I am trying to populate with data from a separate sheet (via a macro) using advanced filter. There are totals and also other data below this range.
    The macro first evaluates how many rows of data will be retrieved, and the inserts (or deletes) rows within the target range, so it is just the right size for the advanced-filtered data.
    Problem: When the advanced filter (using xlFilterCopy) writes in the data, it also ERASES everything in the worksheet BELOW the data!

    For example, headings are on row 5, and I'm pulling in 20 rows of data. Totals and other data are in rows 27 and beyond (let's say rows 27-50). Running the advanced filter pastes in the data in rows 6-25 as expected, but ALL of my data in rows 27-50 is GONE.

    What is going on?!? I see nothing in any documentation on advanced filter stating that it erases data outside of its target range!

    The filter type (xlFilterCopy) implies that the data is first filtered, and then the results are copied (and pasted). But apparently it would more correctly be called xlCopyFilter, as I'm guessing it first copies the ENTIRE set of source data into my target sheet, and THEN filters it. It would be REALLY NICE if the documentation would make this clear (or even mention it at all)!!! But instead, the documentation for the xlFilterAction enumeration for xlFilterCopy says "Copy filtered data to new location". This description is misleading and, I would argue, apparently incorrect.

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: Advanced Filter is Erasing My Other Data!

    What are you specifying for the CopyToRange argument? If you specify a range that will not accommodate all the data (e.g. you just specify the top row) then it will clear everything below that in the column(s).
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: Advanced Filter is Erasing My Other Data!

    Ah, so that is the problem! Yes, I was only specifying the top (header) range as the CopyToRange. I will try it with specifying the entire range.

+ 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. VBA Advanced Filter - Two Filters Without Deleting Bottom Filter Data In Same Column Range
    By hysterical.useless in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2017, 02:54 PM
  2. [SOLVED] Find Count of filter, Advanced filter Data
    By linok in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-06-2015, 04:41 AM
  3. [SOLVED] Filter Table as i type in textbox1 but when erasing the text filter should be unfilter
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-17-2014, 09:08 AM
  4. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  5. Second Advanced Filter does not work based on the data generated from the filter
    By mucc1928 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-29-2011, 02:42 PM
  6. advanced filter a range:Advanced Filter function
    By Il Principe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] why can't i use the advanced filter on my data
    By pinta in forum Excel General
    Replies: 2
    Last Post: 09-03-2005, 02:05 PM

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