VBA to delete rows based on zero in multiple columns

[CenturyBreak]

Hi all... again!

I'm looking for some efficient vba code that will delete rows based on the following criteria: all cells in said row in columns K through OA are zero (numerical value, not the string "zero").

I know I can loop through something like if-and-if-and for each row, but given that I'm trying to whittle down ~ 14,000 rows, that approach might be verrrrryyyyy sloooowwwww.

Another factor is that for this month it will be from K to OA, while in following months the range will be wider. I've already built an index variable for the number of columns in the range to take that into account in future.

Thanks in advance!

 

[chuckles1066]

Public Sub DeleteZeroRows()
'
'define variables
'
Dim i As Long, lastrow As Long
'
'turn off screen updating and automatic calculation
'
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
'
'find last row of data in column K
'
lastrow = Range("K" & Rows.Count).End(xlUp).Row
'
'start at the bottom row
'
For i = lastrow To 1 Step -1
'
'if the sum of K to OA is zero then delete the row
'
    If Application.Sum(Range("K" & i & ":OA" & i)) = 0 Then
        Rows(i).Delete
    End If
Next i
'
'turn on screen updating and automatic calculation
'
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[mirabeau]

here's another one you may like to try

Sub testcode()
Dim u() As Variant, a As Range, rg As Range
Dim c1 As Long, c2 As Long, k As Long, j As Long
Set a = Range(Cells(1), Cells.SpecialCells(11))
ReDim u(1 To a.Rows.Count, 1 To 1)

c1 = a.Columns("K").Column
c2 = a.Columns("OA").Column
Set rg = a.Columns(c1).Resize(, c2 - c1 + 1)

a.Columns(c2).Offset(, 1).Insert
For Each q In rg.Rows
    k = k + 1
    If Application.CountIf(q, 0) = c2 - c1 + 1 Then
        u(k, 1) = 1
        j = j + 1
    End If
Next q
a.Columns(c2).Offset(, 1) = u
Set a = Range(Cells(1), Cells.SpecialCells(11))
a.Sort a.Columns(c2 + 1), 1, Header:=xlNo
a.Resize(j).Delete xlUp
a.Columns(c2 + 1).Delete xlToLeft

End Sub

 

[CenturyBreak]

Thanks folks!

I'll look at these this AyEm.

'if the sum of K to OA is zero then delete the row

Now why didn't I think of that?!?!

 

[mirabeau]

Thanks folks!

I'll look at these this AyEm.

'if the sum of K to OA is zero then delete the row

Now why didn't I think of that?!?!

Could it be because if you've got +1 in Col K, -1 in Col L and zeros elsewhere then you'll delete the row even though it doesn't meet your deletion criterion?

Or, there's very, very, many ways that a set of numbers can sum to zero without each one being zero.

 

[CenturyBreak]

Could it be because if you've got +1 in Col K, -1 in Col L and zeros elsewhere then you'll delete the row even though it doesn't meet your deletion criterion?

Or, there's very, very, many ways that a set of numbers can sum to zero without each one being zero.

Of course... but chuckles read my mind without me knowing it: with this set of data, it's highly improbable (though minutely possible) that there will be any negative values.

 

[ret44]

Not sure what you're analyzing or whether it matters if you have accidental deletions - but it's generally better to assume that the wrong thing will happen, regardless of how improbable it is.

 

[mirabeau]

Of course... but chuckles read my mind without me knowing it: with this set of data, it's highly improbable (though minutely possible) that there will be any negative values.

Hey,

It helps in this business not to have to be too much of a mindreader (just joking )

But if you like sums rather than counting, it would be generally safer (here I agree with ret44's comment) to calculate the sum of squares of your numbers, or the sum of their absolute values.

But since your main original aim was to find efficient codes, you might still like to try the one I posted above. And if you have a strong preference for whatever reason for summing rather than counting, you can change this line

If Application.CountIf(q, 0) = c2 - c1 + 1 Then

to

If Application.SumSq(q) = 0 Then

or

If Application.Sum(Abs(q)) = 0 Then

or even, if you're really set on summing the individual values as they stand

If Application.Sum(q) = 0 Then

... of course it's entirely your problem, but if it were me I'd really avoid the last one.