Delete rows with multiple columns with 0 value
 

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.

Delete rows with multiple columns with 0 value
 

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.

"Tasha" wrote:

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.

Delete rows with multiple columns with 0 value
 

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 wrote:

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.

--

Dave Peterson

Delete rows with multiple columns with 0 value
 

I would rather use a macro, there are 100's of rows and that would take too
long even with filtering. Thanks though.... anyone else have a macro that
would help?

"Sean Timmons" wrote:

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.

"Tasha" wrote:

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.

Delete rows with multiple columns with 0 value
 

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" wrote:

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 wrote:

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.

--

Dave Peterson

Delete rows with multiple columns with 0 value
 

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?

Tasha wrote:

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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson

Delete rows with multiple columns with 0 value
 

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
---------------
Jacob Skaria


"Tasha" wrote:

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.

Delete rows with multiple columns with 0 value
 

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!!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Tasha" wrote:

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.

Delete rows with multiple columns with 0 value
 

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!!!

"Dave Peterson" wrote:

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?

Tasha wrote:

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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson