to copy data in excel and omit blank cells

Posted on 2011-04-19
Last Modified: 2012-05-11

Could you provide me a macro which copies data from a specific range to a specific range and omit the blank cells while copying ?

Please find enclosed sample of the data and the needed  format.

Question by:alcuino
    LVL 85

    Accepted Solution

    Try this:
    Sub DoCopy()
       With Range("A1:L5")
          .Copy Range("P1")
          Range("P1").Resize(.Rows.Count, .Columns.Count).SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftToLeft
       End With
    End Sub

    Open in new window

    LVL 31

    Expert Comment

    by:Rob Henson
    Do you need the blank cells actually omitted or do you want to avoid overwriting existing data because of blank cells in the source data?

    If the latter, you can do a paste special and tick the box for Skip Blanks. You can then copy the whole range and paste in one go but where the copied range was blank it will not paste anything into the destination range.

    Rob H

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now