+ Reply to Thread
Results 1 to 14 of 14

How to Skip blank cells SEE PIC

  1. #1
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    How to Skip blank cells SEE PIC

    Hi
    I am trying to copy all values from Column A and copy it to Column C but without anyspace
    Here is Image of what I am trying to do
    skip.png

    Thanks

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: How to Skip blank cells SEE PIC

    Hi try to
    1.select your range
    2. press F5
    3. select "constans"
    4. copy
    5. paste special
    6. select skip blanks

    p.s. I not sure of proper translation because I do not use English version of excel
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    Re: How to Skip blank cells SEE PIC

    is there a way to do using formula?

  4. #4
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: How to Skip blank cells SEE PIC

    look into attachment (array formula accepted with CTRL + SHIFT + ENTER)
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: How to Skip blank cells SEE PIC

    Sorry. You use ex 2003 so in attachment proper version for your ex.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    Re: How to Skip blank cells SEE PIC

    I see that formula is working for you, in your example, but I don't know the range of the column
    it could be different everytime.

  7. #7
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    Re: How to Skip blank cells SEE PIC

    bumping it up only

  8. #8
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: How to Skip blank cells SEE PIC

    maybe any sample (excel sample not pic.)? guessing is not good idea

  9. #9
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    Re: How to Skip blank cells SEE PIC

    sample1.xlsx
    Please see the attachement, I don't care about zeros, they are on bottom, I don't not want 0's in new column list and do not want blank spaces.

    Thanks

  10. #10
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: How to Skip blank cells SEE PIC

    My formula works also on your data (see attachment) but with small changes
    Attached Files Attached Files

  11. #11
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: How to Skip blank cells SEE PIC

    using the following formula, you can start in any cell (of course other than column A) for any range of cells in column A. however, for performance consideration, using a definite range of cells (such as A1:A10000), instead of the A:A used below, is highly recommended:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER.

    this formula drops '0' from the list - it was not very clear whether you wanted them in or not.
    Last edited by icestationzbra; 04-12-2012 at 05:19 PM.

  12. #12
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    Re: How to Skip blank cells SEE PIC

    Quote Originally Posted by tom1977 View Post
    My formula works also on your data (see attachment) but with small changes

    Very nice man, Thanks alot I finally got it working, can you please explain me how your formula works,
    does what if my column is date is from A4:A500 and I have special question about the formula what is this doing ?
    "ROW()-3"


    Thanks alot again
    you are a life saver.

  13. #13
    Forum Contributor
    Join Date
    06-28-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    115

    Re: How to Skip blank cells SEE PIC

    I have little problem formula doesnt work when I change my column A to D, because that file was only sample my actual data is in Coulmn D with blank space
    Thanks

  14. #14
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: How to Skip blank cells SEE PIC

    First of all this array formula so You must confirm this with CTRL+SHIFT+ENTER.
    Data in your sample start in 4th row and part of my formula is small function which is used in array so it is needed to use argument like 1,2,3 inside this etc. And this part of formula "ROW()-3" will return results like 1,2,3 etc when You copy this down across rows.
    I am not sure it is understandable but you can always use commend evaluate formula to understand how it works.

+ 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