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

Jrosen91

New Member
Joined
May 10, 2020
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. Mobile
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
May 10, 2020
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. Mobile
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!
 

Attachments

  • 11797E27-7D9F-4F6C-A9C0-D6F729A10121.jpeg
    11797E27-7D9F-4F6C-A9C0-D6F729A10121.jpeg
    234.1 KB · Views: 2

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I cannot manipulate data in a picture. Please use the XL2BB to upload your sample.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Like Alan suggested, get used to attaching workbooks with a before and after and without personal data instead of a picture.
VBA Code:
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I was waiting on your comment that it worked or not.
The previous code misses one element. Should be as follows.
Code:
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,120
Messages
5,622,851
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top