• 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

    Hui...

  • 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

paradise

Member
Code:
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
Next
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.
 

p45cal

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:
60041
(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.
 

paradise

Member
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
 
Top