• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

Delete Row if Sum equals 0 (VB Code)

I am looking for a code that sums 5 cells that are in a single row.  If those 5 cells together equal 0, then delte the row and then loop to the next row.  Continue looping til end of spreadsheet.

VB Code:
1.  Sum AH2+AQ2+AZ2+BI2+BR2.  If the sum is 0, then delete row.  Loop through entire sheet until no more rows.
2.  Then End Sub
0
kateebebe
Asked:
kateebebe
1 Solution
 
CodeCruiserCommented:
Is that for Excel? What are the columns for cells?
0
 
philip m o'brienCommented:
If for the specific columns you mentioned then use the following:
 
Sub deleteMyRows()
Dim lastRow As Long
Dim n
    lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    For n = lastRow To 1 Step -1
        If Application.WorksheetFunction.Sum(Cells(n, 34), Cells(n, 43), Cells(n, 52), Cells(n, 61), Cells(n, 70)) = 0 Then Cells(n, 1).EntireRow.Delete
    Next
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Add the formula in a column to the right of the data:

=AH2+AQ2+AZ2+BI2+BR2

Thena pply a filter to the data and select the dropdown for the new column and select the zero value in the list of values.

Highlight and delete the rows.

Alternatively use the filter custom option to show only those that don't equal zero.

Cheers
Rob H
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
philip m o'brienCommented:
If you are likely to have text entries or error values in those columns then simply throw in an "on error resume next" line.
0
 
Rob HensonFinance AnalystCommented:
Should ahve added that the formula then needs copying/dragging down to other rows.

If the new formula is in a column next to the last column of complete data, ie no rows with gaps, double click the bottom right corner of the cell and it will fill down for all data.

Cheers
Rob H
0
 
kateebebeAuthor Commented:
Exactly what I wanted it to do.  Thank you for such a quick response!
0
 
philip m o'brienCommented:
Just as a final note, I wanted to see how far I could take your question. The code below, pasted into a module will all you to:
select which columns you want to sum by the column letter
allow any number of columns to be selected
e.g. when prompted you could enter, from your original question:
AH,AQ,AZ,BI,BR
and it would then loop through, as before and delete those which sum to zero.
Equally you could run it for just
AH,AQ,AZ
Or any other length list.
Enjoy.

 
Option Explicit
Private myString As String
Private mySplit As Variant

Sub deleteIfMySelectionisZero()

Dim lastRow As Long
Dim n As Long, a As Long, intIndex As Long
Dim mySum As Long
Dim msg As Variant

On Error GoTo errorCatcher

Application.ScreenUpdating = False
    getInput
    
    intIndex = UBound(mySplit) + 1
    lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

    For n = lastRow To 1 Step -1
        mySum = 0
        For a = 1 To intIndex
            mySum = mySum + Range(mySplit(a - 1) & n).Value
        Next
        If mySum = 0 Then Cells(n, 1).EntireRow.Delete
    Next
    GoTo theEnd:
    
errorCatcher:
    msg = MsgBox("Oops, there's an error in your data, exiting routine now.", _
        vbCritical, "Something went wrong")

theEnd:
Application.ScreenUpdating = True
End Sub

Sub getInput()
    myString = InputBox("Enter your column selections separated by commas e.g. A,B etc")
    mySplit = Split(myString, ",")
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now