Delete entire row if entire range is blank?

Question

Haven't ever had to do this for an entire range, but only per cell for one column, so I need to figure out if this is even right. I want to loop through a column range (E2:S2) and if every cell is blank, then delete the whole row. If there is at least one cell in that range with data, then keep the row.

How could I edit this in order to create that For/Next loop?

Sub DeleteRowsWithEmptyColumnDCell()
    Dim rng As Range
    Dim i As Long
    Set rng = ThisWorkbook.ActiveSheet.Range("E2:S2") ' <- and then loop to next row, etc..

    With rng
        For i = .Rows.Count To 1 Step -1
            If .Item(i) = "" Then
                .Item(i).EntireRow.Delete
            End If
        Next i       
    End With

End Sub

Would I need to add the for/next loop around the rng?

Error 1004 Error 1004 · Accepted Answer · 2019-02-11T14:30:24

Have in mind Lastrow replace .Rows.Count. If needed, change the column for which Lastrow is calculated. For this example i use Column A.

Try:

Option Explicit

Sub DeleteRowsWithEmptyColumnDCell()

    Dim rng As Range, cell As Range
    Dim i As Long, y As Long, DeleteRow As Long, Lastrow As Long
    Dim cellEmpty As Boolean

    With ThisWorkbook.ActiveSheet

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For y = Lastrow To 2 Step -1

            Set rng = .Range("E" & y & ":S" & y)

            cellEmpty = False

            For Each cell In rng

                If cell.Value <> "" Then
                    cellEmpty = False
                    Exit For
                Else:
                    cellEmpty = True
                    DeleteRow = cell.Row
                End If

            Next

                If cellEmpty = True Then
                    .Rows(DeleteRow).EntireRow.Delete
                End If

        Next y

    End With

End Sub

Delete entire row if entire range is blank?

1 Answers