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

It looks like you are wanting to delete the rows that are referenced in formulas in column L that, when summed, have a zero sum. Is that correct?

Can you post the formula in column L?

Kevin

Answer 2

When the code stops and you select Debug, hover the cursor over the variable "addr". I am guessing that it is not a valid address and you need to fix the formulas you have for creating the string. If this is not the case then need the formula as per post by zorvek.

Answer 3

In your sub below (test), the errors are mentioned at respective lines, in caps:

Sub test()

Dim rng As Range, lr As Long, cf As String, cv As Double, addr As String, r As Long

lr = Cells(Rows.count, "L").End(xlUp).Row

For r = lr To 2 Step -1

    Set rng = Cells(r, "L")

    cf = rng.Formula

    cv = rng.Value

    If InStr(cf, "SUM") And cv = o Then

        addr = Mid(cf, InStr(cf, ",") + 1, Len(cf))

        'there seems to be no comma in cf (formula) & hence returns: 0

        MsgBox InStr(cf, ",")

        'returns: =SUM(C2:K2)

        MsgBox addr

        addr = Left(addr, InStr(addr, ")") - 1)

        'returns: -1

        MsgBox InStr(addr, ")") - 1

        'returns: =SUM(C2:K2

        MsgBox addr

        'returns: $L$2

        MsgBox rng.Address

        'GIVES AN ERROR BECAUSE addr IS MISSING THE CLOSING PARENTHESIS ")"

        MsgBox rng(addr).Address

        'GIVES AN ERROR DUE TO ERROR IN addr

        'AND ERROR IN - Range(addr)

        Union(Range(addr), rng).EntireRow.Delete

    End If

Next

End Sub

You may use the corrected sub (test1) below:

Sub test1()

Dim rng As Range, lr As Long, cf As String, cv As Double, addr As String, r As Long

lr = Cells(Rows.count, "L").End(xlUp).Row

For r = lr To 2 Step -1

    Set rng = Cells(r, "L")

    cf = rng.Formula

    cv = rng.Value

    If InStr(cf, "SUM") And cv = o Then

        rng.EntireRow.Delete

    End If

Next

End Sub

Do you have any specific check or purpose for these lines & why check for ","?

addr = Mid(cf, InStr(cf, ",") + 1, Len(cf))

addr = Left(addr, InStr(addr, ")") - 1)

Union(Range(addr), rng).EntireRow.Delete

If you want to check any specific formula in column L then please let us know & we can do an appropriate check for the same. The sub (test1) will check for formulas in column L & if the formula returns 0 (zero) then the entire row is deleted, meaning only cells containing formulas & returning zero are deleted in column L.

Regards,

Amit Tandon

If this response answers your question then please mark as Answer.

Answer 4

My original design was to find 0's both joined by rows and columns to delete. However I couldn't develop a code to do so. So I figure it is best to sum the row then delete if the entire row is "0". This is for a workbook I have to upload into a website for data processing. Currently we manually delete the rows. I'm trying to improve time efficiency. I will try the both versions you have provided. Thank you.

Answer 5

addr is correct. When I hover over it, it shows the address.