+ Reply to Thread
Results 1 to 2 of 2

How to ignore empty cells when getting unique values

  1. #1
    Registered User
    Join Date
    03-15-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question How to ignore empty cells when getting unique values

    Hi
    I have the values below in column A

    Titel
    olle
    fia
    olle
    pelle
    fia
    siv
    fia

    nisse

    in cell D2 I have A1:A65536<>"" and I use the code below

    Please Login or Register  to view this content.
    The result i get in Blad2!D1 is

    Titel
    olle
    fia
    pelle
    siv

    nisse

    I must admit that I don't know how to use CriteriaRange, but is there a way to skip empty cells with AdvancedFilter. The code is for adding unique values to Blad2!D1 every time a new unique value is added in column A but not empty cells or values.

    Thanks
    Fia

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: How to ignore empty cells when getting unique values

    Hello Fia,

    This version of your macro will copy the unique values without blanks. Another way to remove the blanks is to sort the range after it has been copied. Cell D2 should contain "<>", without quotes.
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 03-15-2011 at 08:56 PM.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

+ 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