+ Reply to Thread
Results 1 to 4 of 4

Advanced Filter - Unique Values Macro Returns 1st Value Twice

  1. #1
    Registered User
    Join Date
    01-03-2011
    Location
    Suwanee, GA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Advanced Filter - Unique Values Macro Returns 1st Value Twice

    I apologize in advance if I have broken any of the thread or posting protocol for this site, but I have spent weeks searching forums for an answer on how to fix what is probably a very simple programming issue without any luck. I have tried various arrangements of code to get the same result pulled from different threads on excelforums.com.

    I am filtering about 5,000 rows of data for unique values using a macro (command button) after completing a monthly SQL query. I need that data filtered and then copied to another sheet within the workbook. This part of the code I have posted below works, but I always get the first value twice.

    Both the ranges "filter" and "DlrNo1" have headers, but when the macro runs and the data is filtered and copied from "DlrNo1" to "filter" the range name is changed to "extract" and the first value appears twice and also replaces the header name.

    Thanks for your help in advance!


    Please Login or Register  to view this content.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Advanced Filter - Unique Values Macro Returns 1st Value Twice

    Advanced Filter expects the range of data to have a "title" at the top of the column. The resulting filtered list has that title, too.

    If your data range is missing a title cell at the top, the adv filter invariably puts the first filtered value in as the title, then gives you your results.

  3. #3
    Registered User
    Join Date
    01-03-2011
    Location
    Suwanee, GA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Advanced Filter - Unique Values Macro Returns 1st Value Twice

    The range of data "DlrNo1" has a title/header. The header and range name are the same- "DlrNo1". Do the name of the range and header have to be different in order for this to work?

  4. #4
    Registered User
    Join Date
    01-03-2011
    Location
    Suwanee, GA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Advanced Filter - Unique Values Macro Returns 1st Value Twice

    Fixed. I think what was causing the issue is that the header and the range name were the same. Once I changed the macro from "DlrNo1" to "P1:P10000" the issue was corrected. It is now working and I can go back to being a happy excel programming reverse engineer. Thanks for the help! I adjusted the code to:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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