VBA error when trying to delete row when sum equals o

Question

The address in range is =sum ("C2:K2") and is correct, rng equals 0. but I don't know what it can be to make this statement correct. Please help. Trying to delete row when sum equals 0.

Answer 1

Hi,

scenario is as follows:

1. data in active sheet
2. in row 1 are headers
3. sum formula in column K
4. asterisk in column L
5. I assume that formula is only in column K

result

delete rows that formula returns zero and column L has Asterisk

if so,

make a copy and try this code

Sub Autof_DeleteRows()

'Jan 04, 2016

'data in active sheet

Const sCrit1$ = "0" '<< criteria1, sum = 0

Const sCrit2$ = "*" '<< criteria2, asterisk

Const sC1$ = "K" '<< sum in col. K

Const sC2$ = "L" '<< Asterisk col. L

ActiveSheet.AutoFilterMode = False

Dim r As Long

r = Cells(Rows.Count, sC1).End(xlUp).Row

With Range(sC1 & "1:" & sC2 & r)

.AutoFilter Field:=1, Criteria1:=sCrit1

.AutoFilter Field:=2, Criteria1:=sCrit2

End With

If Range(sC1 & "1:" & sC1 & r).SpecialCells(xlCellTypeVisible).Count > 1 Then

Range(sC1 & "2:" & sC1 & r).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.AutoFilterMode = False

Else

ActiveSheet.AutoFilterMode = False

MsgBox "nothing found"

End If

End Sub

xxxxxxxxxxxxxxxxxxxxxxxxxx

sample

before

after

Answer 2

I changed "sum" to "=sum(" and this worked thanks ! And thanks everyone. I think there's more than one way to write...I'm still learning. Thanks again!