LinkBack URL
About LinkBacks
Hi. How to tell this piece of code to send just the non empty cells?
Thank you
Please Login or Register to view this content.
Hi. How to tell this piece of code to send just the non empty cells?
Thank youPlease Login or Register to view this content.
You need an IF statement in there
Untested, but maybe:
Please Login or Register to view this content.
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 [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
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
ops... sorry in my code it is not "row"... I'll change it just a second
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.
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.
Maybe is not that simple as I thought
may i ask why you complicated your workbook by giving each cell a named reference
mmm... I'm not an expert... but the cells in cadastro sheet wont be one below the other... Does that make sense?
It would seem all you need is
Please Login or Register to view this content.
Are you wanting to take all the info from Cadastro to dados if There is a Id No and If there is a name?
Yes, except blanks to not overwrite formulas in dados if there are any
Hi xlnitwit... it's overwriting the formula in column data2. Cells above have a formula, new rows don't get the formula....
So in your sample all will be copied over except for Cad_3
Yes, so column data2 would pull the formula above from cell D3
It doesn't do that with your sample workbook as long as you have a blank cell for that data.Originally Posted by 80Wil
So it will always overwrite that formula... even if the macro says to skip, in that case, Cad_3?
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.
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
Have a look at attached workbook. have amended code so i could understand...
Please Login or Register to view this content.
Last edited by Sintek; 04-26-2017 at 01:30 PM. Reason: Added sample workbook
Sorry xlnitwit I dont know the mess I made in my head... but your solution it's working no restrictions... 100% working !
Hi sintek. Worked, but when I push the button again it overwrites the data in row 1.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks