Remove Row if two cells are blank from a result on another sheet

[Jrosen91]

Hi all
My excel knowledge is beginner at best! Need your help!
I want to remove a complete row if cell B5 and below, and D5 and below are BOTH blank or show " " result from another worksheet.

Many thanks

 

[alansidman]

Not sure what you mean "and below." Suggest you be more explicit. Does that mean the whole column? 2 rows? 3 rows? Maybe you should upload a sample of before and after scenarios employing the XL2BB function available on this site.

 

[Jrosen91]

Apologies.
B5 to B100 @ D5 to D200

if there is an empty cell or “ “ in both corresponding cells for that row then delete. I will attach an image. In the example attached I would want rows 14, 16, 17, 19, 20 deleted as there is not a value in both B & D cells for that row. Row 13 would remain as there is a value in D13

like I said my knowledge is very basic, apologies!

 

[alansidman]

I cannot manipulate data in a picture. Please use the XL2BB to upload your sample.

 

[jolivanes]

Like Alan suggested, get used to attaching workbooks with a before and after and without personal data instead of a picture.

Sub Maybe()
Dim i As Long
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        With Cells(i, 1).Offset(, 1)
            If .Value = "" Or .Value = Chr(32) And .Offset(, 2).Value = "" Or .Offset(, 2).Value = Chr(32) Then Cells(i, 1).EntireRow.Delete
        End With
    Next i
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

I was waiting on your comment that it worked or not.
The previous code misses one element. Should be as follows.

Sub Maybe()
Dim i As Long
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        With Cells(i, 1).Offset(, 1)
            If .Value = "" And .Offset(, 2).Value = "" Or .Value = "" And .Offset(, 2).Value = Chr(32) Or .Value = Chr(32) _
                And .Offset(, 2).Value = Chr(32) Or .Value = Chr(32) And .Offset(, 2).Value = "" Then Cells(i, 1).EntireRow.Delete
        End With
    Next i
Application.ScreenUpdating = True
End Sub

Or you can go a different route. This would be faster on a larger range.
Change Sheet, Range and/or Cell references where required.

Sub Maybe_2()
Dim lr As Long, a, aa, i As Long, j As Long, jj As Long
Dim k As Long, cnt As Long, kk As Long, l As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:D" & lr).Value
Application.ScreenUpdating = False
    For i = LBound(a) To UBound(a)
        If a(i, 2) = "" And a(i, 4) = "" Or a(i, 2) = "" And a(i, 4) = Chr(32) Or a(i, 2) = _
            Chr(32) And a(i, 4) = "" Or a(i, 2) = Chr(32) And a(i, 4) = Chr(32) Then
        For j = 1 To 4
            a(i, j) = ""
        Next j
        End If
    Next i
   
    For k = LBound(a) To UBound(a)
        If a(k, 3) <> "" Then cnt = cnt + 1    'MsgBox a(k, 3)
    Next k
   
    ReDim aa(1 To cnt, 1 To 4)
   
    For kk = LBound(a) To UBound(a)
        If a(kk, 1) <> "" Then
            l = l + 1
           
        For jj = 1 To 4
            aa(l, jj) = a(kk, jj)
        Next jj
       
        End If
    Next kk
Sheets("Sheet1").UsedRange.Offset(1).Delete Shift:=xlUp
Range("A2").Resize(UBound(aa), 4) = aa
Application.ScreenUpdating = True
End Sub