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

Sub RowDelete()
    
    Dim Lrow As Long
    Dim i As Long
    Dim Data As Range
    
    Set Data = Range("D2:Y4595")
       
    Lrow = Data.Rows.Count
    Lrow = Lrow + Data.Row - 1
    Application.ScreenUpdating = False
    For i = Lrow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Rows(i), "<>0") = 0 Then
        Rows(i).Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Anyone has a clue what I am doing wrong?

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

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

Quote from youtocc

What about this?

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

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

Quote from norie

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.

Display More

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

Quote from norie

[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]

Display More

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

Robert