Delete rows with multiple columns with 0 value

[Tasha]

On my spreadsheet, I want to delete rows that have 0 values in columns C
through N. If any of these columns have something other than 0, I need them
to stay. Can someone help me with a macro that would do this? I found a
post that would do this for 2 columns, but I couldn't figure out how to do it
for more than 2 columns.

 

[Sean Timmons]

You could just use a formula.

=if(countif(c2:n2,0)=12,"X","")

So, if you have 0 in ALL columns between c and n, you will have an x.
Otherwise you'll have a blank.

then you filter by this column, select x and delete rows.

 

[Dave Peterson]

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'12 columns is C:N
'.resize(1,12) means 1 row by 12 columns
Set myRng = .Cells(iRow, "C").Resize(1, 12)
If Application.CountIf(myRng, 0) = myRng.Cells.Count Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

 

[Tasha]

Thanks Dave!!! to my rescue yet again
well, not sure what it's doing, but doesn't look like it's doing anything.
The 0 rows aren't deleted. I click on the button I assigned the macro to and
it doesn't do anything. Maybe I left out something in my description???
This is how my sheet is set up:
A B C D E F
......................... N
PHYNO PHYNAME JAN FEB MAR APR etc.. through DEC
000001 A.DOCTOR 123 0 141 264 ...ETC....
000002 B.DOCTOR 98 2 4 82 ..................
000003 C.DOCTOR 0 0 0 0 .......... 0
000004 D.DOCTOR 0 4 0 0 ............. 0

so for this example, the only row that would be deleted would be for
C.DOCTOR
which in my sheet is row 5. Anything that has more than 0 stays.

If your macro is set up like that, then I must have copied something over
wrong. I changed my test tab to be Sheet1 so it would be the same sheet
name.... is there something I'm not doing right? Sorry, I'm not real
familiar with coding just yet, but I'm learning!

 

[Dave Peterson]

First, you could have changed the line of code so that it used the correct
worksheet name:

Set wks = Worksheets("Sheet1")
to
Set wks = Worksheets("yoursheetnamegoeshere")

Second, are you sure that the values in those cells are really 0? Maybe they're
small numbers that are formatted to hide the decimal places????

And you're sure that you wanted 12 columns checked for 0's--not empty
cells--really 0's, right?

 

[Jacob Skaria]

Try the below. All cells should be zero (not blank)

Sub Deleterows()
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountIf(Range("C" & lngRow & ":N" & lngRow), "0") _
= 12 Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes

 

[Tasha]

That worked. The other one may have worked also, I finally figured out that
the July through December columns weren't pulling anything so they were blank
instead of 0's.... I filled all those in with 0's, reran macro and it
worked.... Thanks for your help!!!

 

[Tasha]

I finally figured out that the July through December columns weren't pulling
anything so they were blank instead of 0's.... I filled all those in with
0's, reran macro and it worked.... Thanks for your help!!!