Delete row if all cells in a certain range are 0 or blank

[excelgeek1234]

Hello,

I am trying to write a code which looks at rows 13-33 and deletes an entire row if ALL the cells in Columns B-M are 0 or blank and Column A is not blank.

Right now my code deletes the entire row if Column B is 0; doesn't look at the other columns.

I know I need to insert a loop somewhere.

Any help would be greatly appreciated.

Following is the code:



Sub scheduleA()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Schedule A Template").Select

Dim RowstoDelete As Long
x = 33
For RowstoDelete = Cells(x, 2).End(xlUp).Row To 13 Step -1

If (Cells(RowstoDelete, 2).Value = "0") And (Cells(RowstoDelete, 1).Value <> "") Then
Rows(RowstoDelete).Delete Shift:=xlUp
End If


Next RowstoDelete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

[davesexcel]

You have it looking at column A
` (Cells(RowstoDelete, 1).Value <> "") Then`
change the 1 to the "M" column

[johnnymac]

Excelgeek did want to check column A. It is the first part of the And statement that only looks at column B, when the B:M range is desired.

Depending on the type of values that might be found in this range there are several methods than would work. The simplest in my mind would be to SUM the range, but this would fail if you had values of -1 and 1, for example, in that range. This would not require a loop, just a sum where you have (Cells(RowstoDelete, 2).Value = "0"). Let us know what values might be in those columns.

[davesexcel]

Ah yes, you are correct @jonnymac

[excelgeek1234]

The values in these columns are monetary values..$0 and above. I only want to delete the entire row if ALL columns (B-M) have $0. I want to keep the row if it is $0 for the first few columns but say in column F we see a number ($250).

[johnnymac]

OK then, since all values are positive a SUM function would work. The Sum of columns B:M would return 0 only if all were blank or 0. I am fairly new to VBA (it is my 12th programming language, so I tend to mess up syntax and VBA has many ways to do the same task) but I think you can replace the Cells(RowstoDelete, 2) with WorksheetFunction.Sum (Cells(RowstoDelete,2), Cells (RowstoDelete, 13))

Or maybe you don't need the "WorksheetFunction." Sum () may be a native function you can use/