copy paste without blank values

  • Dear Team,


    I am doing copy paste range from one sheet to another sheet.


    For that i am using the following code.


    Code
    1. Sheets("DP").Range("K" & lastrow + 1 & ":" & "K" & lastrow + 15).Value = Sheets("Training").Range("B8:B23").Value

    On my above range there is blank cells are available.


    I want the code should copy the value without blank cells.


    Can any one help me for this issue

  • Your code is not copy/paste.


    try this


  • Dear UncleStringer,


    First time when i am copying your code is updating the values . The problem is, when i copy again this will updating last cell of column K. But other values are updating on after 15th row of B column.


    Code
    1. Sheets("DP").Range("K" & lastrow + 1 & ":" & "K" & lastrow + 15) = Sheets("Training").Range("B8:B23").SpecialCells(xlCellTypeConstants).Value

    The above code is ok. But this copying all the values included blank cells and displaying #N/A.


    Kindly help me with using above code

  • Re: Kindly help me with using above code

    You gave the answer to that yourself. It is not doing what you want.


    Code
    1. Sub Maybe()
    2. Sheets("Sheet2").Range("B8:B23").SpecialCells(xlCellTypeConstants).Copy
    3. Sheets("Sheet2").Range("D1").PasteSpecial xlPasteValues
    4. End Sub

    Change all references where required.

  • Re: Kindly help me with using above code

    You gave the answer to that yourself. It is not doing what you want.


    Code
    1. Sub Maybe()
    2. Sheets("Sheet2").Range("B8:B23").SpecialCells(xlCellTypeConstants).Copy
    3. Sheets("Sheet2").Range("D1").PasteSpecial xlPasteValues
    4. End Sub

    Change all references where required.

    Dear Jolivanes,


    Thanks for your reply.


    I tried the following code. it is working great.

    Code
    1. Sheets("Training").Range("B8:B23").SpecialCells(xlCellTypeConstants).Copy
    2. Sheets("DP").Range("K" & Lastrow + 1 & ":" & "K" & Lastrow + 15).PasteSpecial xlPasteValues
    3. Sheets("DP").Range("K" & Lastrow + 1 & ":" & "K" & Lastrow + 15).BorderAround , xlThin


    And I have tried the following code also, but i am getting error. Is it possible to do with merged cells


    Code
    1. Sheets("DP").Range("K" & Lastrow + 1 & ":" & "K" & Lastrow + 15).Merge
    2. Sheets("Training").Range("B8:B23").SpecialCells(xlCellTypeConstants).Copy
    3. Sheets("DP").Range("K" & Lastrow + 1 & ":" & "K" & Lastrow + 15).PasteSpecial xlPasteValues
    4. Sheets("DP").Range("K" & Lastrow + 1 & ":" & "K" & Lastrow + 15).BorderAround , xlThin

    Can you please check and confirm me

  • Dave is right ...


    In addition ...


    Already indicated to you ... Merged Cells should just be forbidden ...:cursing::evil:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)