+ Reply to Thread
Results 1 to 23 of 23

How to tell this 1 line of code to skip blanks

  1. #1
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    How to tell this 1 line of code to skip blanks

    Hi. How to tell this piece of code to send just the non empty cells?

    Please Login or Register  to view this content.
    Thank you

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: How to tell this 1 line of code to skip blanks

    You need an IF statement in there

    Untested, but maybe:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: How to tell this 1 line of code to skip blanks

    Hi 80Wil

    Perhaps upload a sample file so that we can see what we are dealing with.
    Your code as is gives no value for row or Range("Cad_")
    Are you wanting to copy the entire row or just a cell value?
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Hi PFDave. I'm getting a runtime error 424 object required, and excel is highlighting this line

    shtDados.Cells(Row, "A").Offset(0, a) = shtCadastro.Range("Cad_" & a).Value

  5. #5
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    ops... sorry in my code it is not "row"... I'll change it just a second

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to tell this 1 line of code to skip blanks

    Hi,

    Assuming you don't want to leave blank cells in the target range, you'll need a separate counter variable for the output row number offset.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    I have attached a sample.
    The point to skip blanks is in case I need a column with a formula so the macro wont paste a blank cell over the formula... for eg column data2 Dados sheet.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Maybe is not that simple as I thought

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: How to tell this 1 line of code to skip blanks

    may i ask why you complicated your workbook by giving each cell a named reference

  10. #10
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    mmm... I'm not an expert... but the cells in cadastro sheet wont be one below the other... Does that make sense?

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to tell this 1 line of code to skip blanks

    It would seem all you need is
    Please Login or Register  to view this content.

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: How to tell this 1 line of code to skip blanks

    Are you wanting to take all the info from Cadastro to dados if There is a Id No and If there is a name?

  13. #13
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Yes, except blanks to not overwrite formulas in dados if there are any

  14. #14
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Hi xlnitwit... it's overwriting the formula in column data2. Cells above have a formula, new rows don't get the formula....

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: How to tell this 1 line of code to skip blanks

    So in your sample all will be copied over except for Cad_3

  16. #16
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Yes, so column data2 would pull the formula above from cell D3

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to tell this 1 line of code to skip blanks

    Quote Originally Posted by 80Wil View Post
    Hi xlnitwit... it's overwriting the formula in column data2. Cells above have a formula, new rows don't get the formula....
    It doesn't do that with your sample workbook as long as you have a blank cell for that data.

  18. #18
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    So it will always overwrite that formula... even if the macro says to skip, in that case, Cad_3?

  19. #19
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    In my head... the formula should say... If any of the Cads are empty do nothing with that empty Cad and continue with next Cad.
    But I don't know if its that simple in vba language.

  20. #20
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Hey xlnitwit, I realised that your solution works but if the formula I have is this one =if(cell<>"";cell;"") stops working... I'll try with IFERROR instead of IF

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: How to tell this 1 line of code to skip blanks

    Have a look at attached workbook. have amended code so i could understand...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 04-26-2017 at 01:30 PM. Reason: Added sample workbook

  22. #22
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Sorry xlnitwit I dont know the mess I made in my head... but your solution it's working no restrictions... 100% working !

  23. #23
    Forum Contributor
    Join Date
    11-02-2016
    Location
    brasil
    MS-Off Ver
    office 365 2016 windows
    Posts
    195

    Re: How to tell this 1 line of code to skip blanks

    Hi sintek. Worked, but when I push the button again it overwrites the data in row 1.

+ 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. How to skip line in Code
    By toci in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-06-2016, 01:39 AM
  2. Skip Blanks
    By juanp21 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2015, 03:06 PM
  3. Skip Blanks on Line Graph or Scatter Plot (Excel 2007)
    By Ctolson2344 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-15-2013, 04:04 AM
  4. Skip Blanks
    By Streng in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2009, 04:12 PM
  5. Skip blanks
    By Art MacNeil in forum Excel General
    Replies: 6
    Last Post: 01-15-2006, 02:55 PM
  6. Replies: 0
    Last Post: 02-22-2005, 12: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