Delete entire rows when cells in multiple columns are blank or 0

Question

I am trying to write a code which basically looks at rows 13-33 and deletes the entire row if the cells in Columns B-M are all Blank AND column A is NOT blank.

The first code which I have written below deletes the entire row only if the cell in Column B is blank but I need all the cells in B-M to be blank in order to delete the entire row.

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

I tried writing it differently as well in the following code but can't achieve the desire result.

Sub DeleteRows()


Dim i As Integer

For i = 33 To 13 Step -1
  If WorksheetFunction.CountA(Range("B" & i, "M" & i)) = 0 And WorksheetFunction.CountA(Range("A" & i)) <> "" Then
     Rows(i).EntireRow.Delete
    End If

Next i

End Sub

Please help!

instead of And WorksheetFunction.CountA(Range("A" & i)) <> "" just do And Range("A" & i) <> "" — Scott Craner

Excelosaurus Excelosaurus · Accepted Answer · 2017-11-01T23:04:28

Your conditions for row deletion are: column A not blank, columns B to M blank. Then something like this should do the trick:

Sub ScheduleA()
    On Error GoTo errHandler

    Const TOP_ROW As Long = 13
    Const BOTTOM_ROW As Long = 33

    Dim rowIndex As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With ThisWorkbook.Worksheets("Schedule A Template")
        For rowIndex = .Cells(BOTTOM_ROW, "A").End(xlUp).Row To TOP_ROW Step -1
            If Not IsEmpty(.Cells(rowIndex, "A").Value2) Then '...column A is not blank.
                If Application.WorksheetFunction.CountA(.Range(.Cells(rowIndex, "B"), .Cells(rowIndex, "M"))) = 0 Then '...all cells on row rowIndex from columns B to M are blank.
                    .Rows(rowIndex).Delete Shift:=xlUp
                End If
            End If
        Next
    End With

Cleanup:
    On Error Resume Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Exit Sub

errHandler:
    MsgBox Err.Description, vbExclamation + vbOKOnly, "Error"
    Resume Cleanup
End Sub

Note that the .Select is gone; you almost never have to select anything to get the job done. Not relying on the selection will make your code much more robust. In the code above, the With block tells the code within it to refer to the target worksheet whenever an expression starts with a period, such as .Cells.

Also, when turning off ScreenUpdating and Calculation, systematically include error handling to turn them back on. This way, if something goes wrong, your code won't leave Excel in an undesirable state.

Finally, instead of referring to worksheets by their tab's name (as seen from Excel), you can refer to them directly using their CodeName, as seen from the VBA editor, in the Properties window, under the worksheet's (Name) property (press Ctrl+R to show the Project Explorer, click on the worksheet under the Microsoft Excel Objects node, then press F4 to display the Properties window). You can change this value; I'd typically change it to shtScheduleATemplate. Then, the With line could be re-written as:

With shtScheduleATemplate

...which would still work even after you changed the worksheet's name from Excel.

EDIT: in your question's code, you are checking column B when determining at which bottom row index to start the loop. However, by doing so, you may miss some rows that should be deleted. I've changed my answer to check within column A instead:

For rowIndex = .Cells(BOTTOM_ROW, "A").End(xlUp).Row To TOP_ROW Step -1

Delete entire rows when cells in multiple columns are blank or 0

1 Answers