+ Reply to Thread
Results 1 to 3 of 3

Extract Unique Values from List using VBA

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    48

    Extract Unique Values from List using VBA

    Hi,

    I have a sheet where Column A is updated with individual names from a Names List on a regular basis and so there will be lots of repetition as the list gets bigger. I need to extract the unique names and show them in a different column. I've scoured the internet and every formula that I have found doesn't seem to work so I thought some VBA / Macro (not my forte) would work. So far, in a Worksheet_Change sub I have:

    Please Login or Register  to view this content.
    Which works, UNLESS:

    1) as soon as the data from Column A is deleted (i.e. if entered by mistake and deleted rather than replaced) OR
    2) any other worksheet change event (such as entering data elsewhere in the worksheet)

    I get a Run-time error '1004': "the extract range has a missing or illegal field name". This shouldn't be a problem too often as 'A' should be the first data entered, but as it's not just going to be me using this, I'd like to put something in place that fixes this but my coding skills are not great.

    Any help would be much appreciated.

    Thanks,
    Last edited by AliGW; 01-03-2018 at 04:20 AM. Reason: Code tags corrected.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extract Unique Values from List using VBA

    Without seeing your data layouts, not much we can do.
    Please Login or Register  to view this content.
    If this doesn't work, upload a workbook showing before/after.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Extract Unique Values from List using VBA

    Hello Musicman1985,

    Try "locking down" the column as follows:-


    Please Login or Register  to view this content.
    The extraction part of the code is basically the same as yours. I just used some abbreviations.


    If it still doesn't work, then please do as Jindon has asked.

    I hope that this helps.

    Cheerio,
    vcoolio.

+ 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. [SOLVED] Extract list of unique values, only if they have specific text next to them.
    By Who_else in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-12-2017, 04:36 PM
  2. Replies: 4
    Last Post: 01-04-2017, 07:29 PM
  3. Extract unique text values from a filtered list
    By ljerromes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2015, 07:51 PM
  4. [SOLVED] automatically extract unique values from a list
    By labogola in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-07-2014, 10:54 AM
  5. [SOLVED] Extract unique values from a table and list along a row
    By doctorblyth in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2013, 04:33 PM
  6. [SOLVED] Extract Unique Values from Long list and Put in Sorted Order
    By snapfade in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-08-2013, 09:10 PM
  7. [SOLVED] Extract unique values out of list based on an extra criteria
    By sven1975 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 05:02 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