• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy all visible cells excluding blank and zeros


Sub belle()
Dim i As Long
For i = Cells(Rows.Count, 3).End(xlUp).Row To 6 Step -1
    If Cells(i, 8) = 0 And Cells(i, 9) = 0 And Cells(i, 10) = 0 And Cells(i, 11) = 0 Then Range("C" & i).EntireRow.Delete
End Sub
I want copy those visible cells excluding above condition which I have mentioned in above code.(Code extracted from other posts).Those data will not be copied if as mentioned in above code that is either blank or zero. to another sheet "Data".

Luke M

Excel Ninja
Since you've already got a filter applied in a way (you're checking for visible cell), then I'd just add another criteria to the filter to hide the cells with 0 and blank. Then it's a simpler copy & paste.


Well-Known Member
Are you saying that once you've removed rows that contain all blank/zeroes in columns H to I, you want to copy the result to another sheet, but where there is the occasional blank cell (or zero-containing cell) you don't want that blank value overwriting data that may already be in place where you're copying to?
If so, you can copy/paste-special and skip blanks:
(which can be coded).
I don't think you can skip zeroes in the same way, but you could have a two-stage process where you first copy to an intermediate location, search/replace zeroes with blanks, then copy/paste-special skipping blanks to the final location.
I'll write some code to do that if I've understood you correctly.


I think if someone would edit the code as being >0 and does not contain blank -

If Cells(i, 8)> 0 And Cells(i, 9) >0 And Cells(i, 10) >0 And Cells(i, 11) >0 Then Range("C" & i).EntireRow.copy
and paste to another worksheet