Delete Row if Cells in 3 columns are blank

[pkew22]

I would like to delete a row, if the cells in columns D, F, K of that row are blank. All three cells must be blank.

Columns A to W have data. Data (heading) starts in row 5. Number of rows varies

How do I do that?

Thank you.

 

[Momentman]

Maybe something like this

Sub DeleteRows()
    For I = Range("A" & Rows.Count).End(xlUp).Row To 5 Step -1
        If Range("D" & I) = "" And Range("F" & I) = "" And Range("K" & I) = "" Then
            Rows(I).EntireRow.Delete
        End If
    Next I
End Sub

 

[Rick Rothstein]

Assuming the values in Column D are constants (that is, not formulas), give this macro a try...

Sub DeleteRowsIfCellsAreEmptyInColumnsDandFandK()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
  Range("[B][COLOR=#ff0000]D[/COLOR][/B]5:[B][COLOR=#ff0000]D[/COLOR][/B]" & LastRow) = Evaluate(Replace("IF(LEN(D5:D@&F5:F@&K5:K@)," & _
                            "IF(LEN([COLOR=#ff0000][B]D[/B][/COLOR]5:[COLOR=#ff0000][B]D[/B][/COLOR]@),[COLOR=#ff0000][B]D[/B][/COLOR]5:[B][COLOR=#FF0000]D[/COLOR][/B]@,""""),""#N/A"")", "@", LastRow))
  On Error Resume Next
  Columns("D").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

If Column D has formulas, then use either F or K in place of the 6 red D's above depending on whether F or K are constants.

 

[pkew22]

Thank you. Works beautifully.