Copy Non-Blank rows in a Range and Paste to Other Sheets

  • Hello,



    I have this code that copy range and paste to other sheets:


    However, when the copy range has only values in range A105:F105 and range below are blanks, it copies all range to worksheet("Report"). And when i add another data, it started in the row where the upper row is empty.


    What is the missing code that only copy range with values and paste it to worksheet("Report")? So that when i add another data, it will paste next to it...


    Thanks.

  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    Hi luzmen
    something like,,,,

    Code
    1. Set copySheet = Worksheets("Data")
    2. Set pasteSheet = Worksheets("Report")
    3. dim lngRow as long
    4. lngRow=copySheet.Cells(Rows.Count, 1).End(xlUp).Row
    5. copySheet.Range("A105:F"&lngRow).Copy
  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    Quote from pike;794903

    Hi luzmen
    something like,,,,

    Code
    1. Set copySheet = Worksheets("Data")
    2. Set pasteSheet = Worksheets("Report")
    3. dim lngRow as long
    4. lngRow=copySheet.Cells(Rows.Count, 1).End(xlUp).Row
    5. copySheet.Range("A105:F"&lngRow).Copy


    pike,



    Thank you so much for the reply.. but the code still didn't work, it copies the blank rows that included in the Range A105:F110.


    By the way, range A105:F110 has a formula function on every cell, the function looks like this: =if(cellvalue="","",cellvalue).
    Im so sorry i forgot to tell, maybe this is the reason why the blank rows were always included...

  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    Please attach your workbook.
    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    Yes, those formulas were the cause of the issue. Try this:

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    Thanks KJBox, It Works!


    But i've noticed something, when there is no data in the range, there is a Run time Error at this portion

    Code
    1. .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(UBound(y, 2), 6) = Application.Transpose(y)


    There is a possibility that no data for Spouse Business/Work Information.

  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    This has a code line to check that there is data to copy.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    Quote from KjBox;794981

    This has a code line to check that there is data to copy.



    Thank you so much!! Works Perfectly!!!

  • Re: Copy Non-Blank rows in a Range and Paste to Other Sheets


    my loop less alternative

  • This is my first post so “Hi to everyone” I am also quite new to VBA , I have read the thread here and using Kjbox code above it works fine, in my scenario my source data is in workeet2 column Z, the column contains an extracted list of data of undefined length up to 100 entries and contains cells with data and blanks). I want to copy non blank cells over to worksheet 1 with the 1st entry copied starting in B3. What I’m not sure is how to define the row, column in the worksheet? Appreciate if someone could guide me Kind regards Phil

  • Unfortunately your post does not comply with our Forum RULES. Do not post a question in the thread of another member -- start your own thread.


    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.


    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.