# Row Delete by Countif function

• Hello,

I am trying to remove rows if a certain condition is met. More specifically if the row has no value ie only zero the the row should be removed. I am using the countif function to check if the row only has zeros (and if it does then removing it), in the worksheet the formula looks like this:

COUNTIF(D25:X25;"<>0") ie any cell that renders the amount zero should be removed.

I am toying with the following code but I does not work..

Anyone has a clue what I am doing wrong?

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Try

If Application.COUNTA(Rows(i)) - Application.COUNTIF(Rows(i),0) = 0 Then

HTH

Bob

• Re: Row Delete by Countif function

Quote from norie

How does it not work?

Well it does not do anything..ie nothing happens to the rows!

Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Quote from Bob Phillips

Try

If Application.COUNTA(Rows(i)) - Application.COUNTIF(Rows(i),0) = 0 Then

Nope nothing happens..

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Are you sure this is getting the correct value for Lrow?

Code
``````Lrow = Data.Rows.Count
Lrow = Lrow + Data.Row - 1``````

Boo!:yikes:

• Re: Row Delete by Countif function

Ok,

I have enclosed a workbook with the basic jist of what I am trying to do, but in VBA. Ie if the countif value renders a zero then remove the row. Hope you understand what I mean.
Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Quote from hgus393

Ok,

I have enclosed a workbook with the basic jist of what I am trying to do, but in VBA. Ie if the countif value renders a zero then remove the row. Hope you understand what I mean.
Robert

My suggestion works fine for me.

HTH

Bob

• Re: Row Delete by Countif function

Robert

Your code appears to be deleting fine for me.:)

Boo!:yikes:

• Re: Row Delete by Countif function

Quote from Bob Phillips

My suggestion works fine for me.

I must be a complete idiot I cannot get it to work. :duh:

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Code
``````Sub DeleteEmptyRows()
Dim LastRow As Long, i As Long
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = LastRow To 1 Step -1
If WorksheetFunction.Sum(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub``````
• Re: Row Delete by Countif function

Quote from youtocc

Code
``````Sub DeleteEmptyRows()
Dim LastRow As Long, i As Long
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = LastRow To 1 Step -1
If WorksheetFunction.Sum(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub``````

Yes that would work fine if I did not have Row headings and if I did not have figures with - signs and plus sign (I could get a sum = 0 for those occasions even though the row has data that I need)

My data looks sort of like this...

Fig1 Fig2
Dogs 0 0
Cats 1 2
Birds 2 1

Then the countif formula cannot be applied.

Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Robert

What exactly are you trying to do?

Could you not just sort blank rows to the bottom?

Boo!:yikes:

• Re: Row Delete by Countif function

Quote from norie

Robert

What exactly are you trying to do?

Could you not just sort blank rows to the bottom?

Hello Norie,

I think it is better that I send the information I have in the worksheet (removing about 4500 rows but still the basics of what it looks like), hopefully thing will get clearer then:

Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Robert

I take it you want to remove the rows where the data under all the dates is 0?

If so why are you using CountIf on the whole row?

It will never equal zero because of the values in columns A-C.

Boo!:yikes:

• Re: Row Delete by Countif function

[vba]
Sub RowDelete()

Dim Lrow As Long
Dim i As Long
Dim Data As Range
Dim rng As Range
Set Data = Range("D2:x38")

Lrow = Data.Rows.Count

Lrow = Lrow + Data.Row - 1

Application.ScreenUpdating = False
For i = Lrow To 1 Step -1
Set rng = Range("D" & i & ":X" & i)
If Application.WorksheetFunction.CountIf(rng, "<>0") = 0 Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub[/vba]

Boo!:yikes:

• Re: Row Delete by Countif function

Yes I want to remove the rows where the data under all the dates is 0.

Hum see what you mean about columns A to C - Do have any clue to get round that?

Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

• Re: Row Delete by Countif function

Sorry was a bit to quick to answer...This did the stuff.. :thanx:
:yourock:

Robert

.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!