+ Reply to Thread
Results 1 to 4 of 4

Ignore empty cells

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Montreal
    MS-Off Ver
    Office 365
    Posts
    12

    Ignore empty cells

    Hi,

    This is my first post to Excel Forum. I consider myself a VB novice... Here is my question. I have that macro to merge the content of a collumn range (email addreses). The macro will add a separator ";" beetween each addresse. I'd like it to ignore empty cells so it doesn't come out with a bunch of ;;;;;;; for the empty cells.

    Thanks for helping

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Ignore empty cells

    Personally I would take the lazy approach and sort column N to get rid of empty cells :\ But I am also pretty novice at VB.

    If you don't need it to be VB, another really lazy way would be to add a new column O, and enter the following;

    Cell O3: =N3
    Cell O4: =N4&";"&O3

    And just copy cell O4 that all the way down, by the time you get to O600 you'll have them all, and none of the blanks will appear. Just copy/paste values and you'll have your email addresses in one line
    Last edited by Speshul; 06-19-2014 at 02:42 PM.

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

    Re: Ignore empty cells

    Hello nomis65,

    Welcome to the Forum!

    The Join method unfortunately does not allow you to skip blank cells. You would need to loop through the cells in the range and test if the cell is empty or not.
    Please Login or Register  to view this content.
    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!)

  4. #4
    Forum Contributor pareshj's Avatar
    Join Date
    05-20-2014
    Location
    MUMBAI
    MS-Off Ver
    2007 & 2010
    Posts
    447

    Re: Ignore empty cells

    Hi,

    Use the following code and hope this will help you :


    Please Login or Register  to view this content.

    Regards,
    Paresh J
    Click on "* Add Reputation" as a way to say thanks

+ 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] Ignore empty cells in an IF formula
    By moppyau in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-09-2013, 06:42 AM
  2. Excel 2007 : Ignore empty cells? How?
    By PA0l0 in forum Excel General
    Replies: 3
    Last Post: 06-02-2011, 07:29 PM
  3. How to get a formula to ignore empty cells
    By scudder12 in forum Excel General
    Replies: 1
    Last Post: 10-02-2010, 06:18 PM
  4. Can a formula ignore empty cells?
    By Melissa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2006, 01:30 PM
  5. [SOLVED] Trendline to ignore empty cells
    By Kara in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-07-2005, 01:05 PM

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