+ Reply to Thread
Results 1 to 36 of 36

edit code to copy only non blank row from one sheet to another

  1. #1
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    edit code to copy only non blank row from one sheet to another

    Dear All expert
    i have the following code that work good but what i need to copy value in range without empty row
    here the code
    HTML Code: 

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: edit code to copy only non blank row from one sheet to another

    Add this after your pastespecial line to get rid of the blank rows.
    Please Login or Register  to view this content.
    This will delete the empty rows from your print sheet.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: edit code to copy only non blank row from one sheet to another

    Quote Originally Posted by xladept View Post
    Maybe:

    Please Login or Register  to view this content.
    If column A has a blank cell but column B does not, then it would delete the row that has data wouldn't it.

  5. #5
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    Dear JLGWhiz
    please its give me error as in attached photo
    Attached Images Attached Images

  6. #6
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    Dear xladept
    Please see my attached photo because its not working
    Attached Images Attached Images

  7. #7
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: edit code to copy only non blank row from one sheet to another

    Quote Originally Posted by mazan2010 View Post
    Dear JLGWhiz
    please its give me error as in attached photo
    It was missing a Parenthesis.
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    Dear JLGWhiz
    Still not working give me the following error
    Attached Images Attached Images

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  10. #10
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: edit code to copy only non blank row from one sheet to another

    Quote Originally Posted by mazan2010 View Post
    Dear JLGWhiz
    Still not working give me the following error
    Remove the 'xlShiftUp' and see if then does the delete. The default shift should be 'Up'.
    Since I do not get an error using that code, I would be interested in knowing what the error message is that you get.
    Last edited by JLGWhiz; 03-24-2018 at 05:19 PM.

  11. #11
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    please kindly find my attached file
    the problem is i use userfrom to enter data in Label Sheet in all yellow cells
    and my formula in rest of cell generate data
    what i need copy data from label sheet to Print sheet without any change in label sheet
    and the data copied in print sheet without any empty row or make one row as seprator between
    every group
    like in my file
    because the code for JLGWhiz is change in Label and remove rows that have function

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 03-24-2018 at 10:18 PM. Reason: Compactness

  13. #13
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: edit code to copy only non blank row from one sheet to another

    Please Login or Register  to view this content.
    The reason it was deleting rows in the 'Label' sheet was because the Rows(i) needed a period in front of it to tie it to the 'Print' sheet. It defaulted to the active sheet without that period. That is also what was causing the error before. With the period in place it should copy your rows to the Print sheet and then delete the empty rows. If the code from xladept does not work for you, you can try the above modified snippet.
    Last edited by JLGWhiz; 03-24-2018 at 09:27 PM.

  14. #14
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    the code doesn't work its give me error
    as in photo
    Attached Images Attached Images

  15. #15
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    the target from my file as the following
    i have 8 cases in column J in label sheet
    and in D2 date according to this date they get corresponding data from ref sheet in range of
    D:J
    meaning it will generate data according to date
    if we select case suppose FFFSE , FFFRO ,FFFLYRO only it make 5 row is empty
    but i need to get this data withouyt this empty row
    if there is any one have another idea to make all this job with easier way i will appreciate
    many thanks for all for attention and concern

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    What does the error say? This code ran on your sample

  17. #17
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    yes on my last file i post 11

  18. #18
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    sorry it work i make new one and try it and it work good

  19. #19
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    but please if i need to add to this code something before add value to print sheet delete previous data in print sheet because
    if i create new data entry i found in the same file previous data

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    If the error is because of your msgbox "not filled in" then you can just run this separately:

    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    how can i add the following to the code
    HTML Code: 
    before copy data from Label sheet
    also if can make this code make only one row empty between each case or not as separator ( if can)

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    Try this:

    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    what about clear code

  24. #24
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    Try this:

    Please Login or Register  to view this content.
    And thanks for the rep!

  25. #25
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    its not working good for clearance

  26. #26
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    What's not working??

  27. #27
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    clearance print sheet before adding data

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    That's what it's supposed to do by post #21???

  29. #29
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    the problem if i make data generation and come again to make another data it not remove previous data in print sheet
    for this reason i want to add to my code line that before copy data from label sheet to print its make clearance for print sheet then copy data to it from label sheet

  30. #30
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: edit code to copy only non blank row from one sheet to another

    But isn't that exactly what the routine does?

  31. #31
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: edit code to copy only non blank row from one sheet to another

    Did you make the correction as noted in post #13? The anomally you noted in post #11 of deleting formulas on sheet Label will be fixed by simply putting a period in front of the Rows(1) in the code snippet. Then use the snippet after the pastespecial line of code.

  32. #32
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    Dear JLGWhiz
    I'm So sorry because I'm not understand you exactly what you mean because my knowledge is very weak in VBA code

  33. #33
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    please see my attached photo with example
    Last edited by mazan2010; 03-26-2018 at 04:14 PM.

  34. #34
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: edit code to copy only non blank row from one sheet to another

    Quote Originally Posted by mazan2010 View Post
    Dear JLGWhiz
    I'm So sorry because I'm not understand you exactly what you mean because my knowledge is very weak in VBA code
    You can disregard it. I was late posting it anyhow. You appear to have resolved that part of the issue.

  35. #35
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    what about how can make the code delete previous data in print sheet before add new data

  36. #36
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,033

    Re: edit code to copy only non blank row from one sheet to another

    Dear
    do you can see my attached file with all code that i try to add your code with clear but not working
    and add button on userform to clear file then use another button to generate data then use another to export data
    that what i need this button for generate and clear be

+ 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. VBA code - copy info from sheet 1 to sheet 2 but NOT COPY BLANK CELLS
    By Yossarian1980 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2017, 06:09 PM
  2. [SOLVED] This code not working in a Protected Sheet, can any one pls edit this code
    By rajeshn_in in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2016, 01:08 AM
  3. [SOLVED] help finish/fix this code: copy non-blank cells to corresponding cell on another sheet
    By Rerock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2016, 01:38 PM
  4. Replies: 5
    Last Post: 12-12-2013, 02:53 PM
  5. [SOLVED] VBA code to copy cell values to the next blank row in another sheet (all the same row)
    By rarias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2013, 03:08 AM
  6. [SOLVED] VB code help- need copy cells in a row and paste to new sheet on next available blank line
    By Barbara Excel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-27-2012, 03:11 PM
  7. Edit code to copy whole sheet
    By Sirishgreen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2008, 08:06 AM

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