Unable to get SUM property of the worksheetfunction class

I had a simple delete rows macro based on a range summing to nil that worked in testing at home and then it didn't when I really need it to work at work..

Code is:

Sub DeleteEmptyRows()
Dim LastRow As Long
    Dim r As Long
    Dim Counter As Long
    Counter = 0
    'Application.ScreenUpdating = False
    LastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row - 1
    For r = LastRow To 4 Step -1
        If Application.WorksheetFunction.Sum(Rows(r)) = 0 Or _
        ActiveSheet.Range("E" & r).Value Like "*" & "Total" & "*" Then
        
            Rows(r).Delete
            Counter = Counter + 1
        End If
    Next r
   ' Application.ScreenUpdating = True
    'DeleteEmptyRows = Counter
    
End Sub

Help please!

Well... could you please post a sample of the looking of your Workbook?

I will do tomorrow morning. I was using it as a Function and calling it from another much larger routine to tidy up the end result (taking any row with "Total" in it or nil value , and it all worked fine when I was testing at home. Im sure I gave it a run a t work as well. I ramble on. I'll post sample of workbook tomorrow.

The line it is hanging on is

If Application.WorksheetFunction.Sum(Rows(r)) = 0 Or _
        ActiveSheet.Range("E" & r).Value Like "*" & "Total" & "*" Then

Hi Aka
Can you explain (in layman's terms) what you are recommending here. Don't you need to give U value before you set the if statement? I have really limited time tomorrow (in the middle of month end and lost several hours today due to various issues, my delete rows macro being 5% of them!). Why do you think this will work? (I don't like black boxes, mysteries etc..!). Thanks in advance for your support.
Chris

Hmmm. Really frustrating. After a very long week I ran the original macro again last night and the pesky thing worked as part of a call function of the larger macro consolidating data from a sample of feed files.
If I get time I'll run some more tests next week on larger set of files to see if I can replicate error. Orin - I'll have a play with the Union code which will hopefully make more sense when I see what it does in action.

Thanks for suggestion.

If I can't replicate the issue I had Ill close the thread.

Chris

Re: Unable to get SUM property of the worksheetfunction class
Don't you have Error value within a row?
If so, try something like

If Application.WorksheetFunction.SumIf(Rows(r),"<>#N/A") = 0 Or

Ahh, Jindon, You may be on to something. The first column of data has a lookup formula in it which, as it is currently written, could return an error value in some circumstances. I'll check it out Monday. Thanks.