Delete Entire Row when two cells are blank

[tlc53]

Hi there,

Data is entered into range A9 to L (row unknown as data keeps being added). I would like a macro to delete the entire row if BOTH the cells in column E and F are blank. If only one is blank, it shouldn't be deleted.
I'm not really hitting the mark so far with my code.

Sub Delete_Blank_Rows()
On Error Resume Next
ActiveSheet.Columns("E").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

This deletes all rows only if column E is blank.

Can someone help me please?

Thank you.

 

[mrshl9898]

Sub Delete_Blank_Rows()

Dim sht As Worksheet
Dim LastRow As Long
Dim rownum As Long


Set sht = ActiveSheet


rownum = 9
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


Do Until rownum = LastRow + 1
If Cells(rownum, 5) = "" And Cells(rownum, 6) = "" Then
Rows(rownum).Delete
rownum = rownum - 1
End If
rownum = rownum + 1
Loop


End Sub

 

[orsm6]

this code works for me....

    'delete blank lines
    Application.ScreenUpdating = False
    For LR = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & LR).Value = "" Or Left(Range("A" & LR), 1) = "`" Then
            Rows(LR).EntireRow.Delete
        End If
    Next LR

it deletes any row with a blank cell in column A, it also deletes any row with ` symbol in column A

 

[Trebor76]

As per VBA when trying to use two or more columns with Special Cells:

"Cannot use that command on overlapping selections"

If your data set isn't too large you could loop backwards through it deleting each row where Col's E and F are blank like so:

Option Explicit
Sub Delete_Blank_Rows()

    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
    
    With Application
        .ScreenUpdating = False
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
    End With
    
    lngLastRow = Range("A:L").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'Work backwards through the rows from the last row found to Row 9.
    For lngMyRow = lngLastRow To 9 Step -1
        If Len(Range("E" & lngMyRow)) = 0 And Len(Range("F" & lngMyRow)) = 0 Then
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With
    
End Sub

HTH

Robert

 

[orsm6]

sorry.... i just noticed what you are asking and my code isn't helpful at all lol.... sorry.

this code works for me....

    'delete blank lines
    Application.ScreenUpdating = False
    For LR = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & LR).Value = "" Or Left(Range("A" & LR), 1) = "`" Then
            Rows(LR).EntireRow.Delete
        End If
    Next LR

it deletes any row with a blank cell in column A, it also deletes any row with ` symbol in column A

 

[tlc53]

Thanks very much Robert! Works a charm!

 

[Trebor76]

Thanks for letting us know and the thanks and like