Solved

Posted on 2011-05-06

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

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

7 Comments

```
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
```

=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

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

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
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**14** Experts available now in Live!