VBA code to find a non-zero number in a row range

[rasinc]

I have some code creating up to 55 or 60 sheets. The final sheets are basically income statements that have accounts on each row and columns for months.

Everything worked fine until the users found some information was missing.

In order to eliminate zero rows (accounts without any dollars), I used the following code

For x = lngLastRow To 6 Step -1
    If Application.WorksheetFunction.Sum(Range("D" & x & ":AH" & x)) = 0 Then
        If rngRangeToDelete Is Nothing Then
            Set rngRangeToDelete = Cells(x, 2)
        Else
            Set rngRangeToDelete = Union(rngRangeToDelete, Cells(x, 2))
        End If
    End If
Next x

If rngRangeToDelete Is Nothing Then
Else
    rngRangeToDelete.EntireRow.Delete
End If

I think I got some help here or on another forum to get the right syntax and it works based on the philosophy that if the total sum of the row is zero, delete it. This was based on an assumption that I would not have data that looked like

0   0   240   0   0   0   -240   0   0   0

So as you can see the sum the row is zero but there is real data there so I cannot delete it (currently it is being deleted).

I've been trying to find a way around this. My first thought was to cycle through each column on each row and look for a non-zero number. If I find it, jump out of the loop and move to the next row. If not, then add it to the range to delete.

I figured there had to be a better way of doing it without a loop but I've been searching the sites for the last hour and can only find looping ideas like I originally had. I'm not against doing it but with so many sheets and upwards of 400 rows, it could take some time even with screen updating, calculations and alerts turned off.

Does anyone have a better idea? TIA, rasinc

 

[Gary McMaster]

Maybe use the "Abs" (Absolute value) function in your summing loop so that negative values come up positive.

Hope that helps.

Gary

 

[rasinc]

Thanks Gary. Sounds like an obvious answer now and I think it would work but how do you do an Abs function on a range of cells? I tried this code below and it gives me a type mismatch.

If Application.WorksheetFunction.Sum([COLOR=#ff0000]Abs([/COLOR]Range("D" & x & ":AH" & x)[COLOR=#ff0000])[/COLOR]) = 0 Then

I think to do this I would have to cycle through each cell in each column anyway to turn each cell to the Abs before doing the sum.

 

[erik.van.geit]

Hi,
You would better use "countif" with "<>0"
kind regards,
Erik

 

[rasinc]

Thanks Erik. I decided to go with the code below

For x = lngLastRow To 6 Step -1
    For y = 4 To 34
        If Cells(x, y) <> 0 Then
            blnZerosFound = False
            Exit For
        Else
            blnZerosFound = True
        End If
    Next y
    If blnZerosFound Then
        If rngRangeToDelete Is Nothing Then
            Set rngRangeToDelete = Cells(x, 2)
        Else
            Set rngRangeToDelete = Union(rngRangeToDelete, Cells(x, 2))
        End If
    End If
    blnZerosFound = False
Next x
    
If rngRangeToDelete Is Nothing Then
Else
    rngRangeToDelete.EntireRow.Delete
End If

It's just about as fast as my original code (a second difference at most), so my concern about speed turned out to be not an issue.

Thanks for the responses.

 

[erik.van.geit]

You're welcome!
It's up to you to decide to use "oneliners" or "loops".