IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW

ChrisOK · #1 04-26-2019, 03:36 PM

Needing to have entire row deleted using VBA IF either of these 2 Rules are TRUE:
Rule #1 IF cell in Col A (is populated with anything) but cell in Col C (is blank)
AND/OR
Rule #2 IF cell in Col D has the #VALUE error present

Conditional Range would apply to: rows 7:40

Hopefully, I'm wording that correctly...
If a row is found where Col A is populated but Col C is blank (delete that row)
.. also
If a row is found where Col D has the #VALUE error present, delete that row too

Thank you in advance!

Alansidman · #2 04-26-2019, 07:09 PM

Maybe this:

Code:

Option Explicit

Sub ChrisOK()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = lr To 1 Step -1
        If Range("C" & i) = "" Then
            Range("C" & i).EntireRow.Delete
        ElseIf Range("D" & i) = "#VALUE" Then
            Range("D" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox ("Complete")
End Sub

MagPower · #3 04-28-2019, 02:30 AM

Quote:

Originally Posted by Alansidman

Maybe this:

Code:

Option Explicit

Sub ChrisOK()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = lr To 1 Step -1
        If Range("C" & i) = "" Then
            Range("C" & i).EntireRow.Delete
        ElseIf Range("D" & i) = "#VALUE" Then
            Range("D" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox ("Complete")
End Sub

I would use Alan's code, but with one modification of the first "If" statement:

If Range("C" & i) = "" And Range("A" & i) <> "" Then

Pecoflyer · #4 04-28-2019, 02:56 AM

@Magpower
Hi and welcome
please do not quote entire posts unnecessarily, they are just clutter.
Thanks

ChrisOK · #5 05-04-2019, 06:41 PM

Thank you (all) for the posts -- I've tried both ways and both are erroring out on the exact same #VALUE line... (see image)
Any ideas how to resolve?

ChrisOK · #6 05-04-2019, 06:48 PM

here's a visual sample of what rows should be removed (highlighted in yellow)

Pecoflyer · #7 05-04-2019, 11:20 PM

@chrisok
Please post a sample sheet instead of useless images ( they also clutter the thread)
Thanks

Alansidman · #8 05-05-2019, 09:00 PM

You can easily do this with Power Query/Get and Transform. Load your file to PQ, filter out column C on null values. Then close and load back to Excel.

or you can change the line of code that is giving you issue to

Code:

ElseIf Iserror(Range("D" & i)) Then

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted	vjvj123	Word VBA	12	09-27-2018 03:09 PM
Fill blank cell with value from adjacent cell	kevinbradley57	Excel Programming	2	04-17-2018 08:40 AM
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow	FUGMAN	Excel Programming	7	02-05-2017 08:37 AM
Need macro to delete a column with a blank cell	dwirony	Word VBA	2	10-20-2016 01:31 PM
How can I delete the content of a cell in column if the cell value is more than 1000?	Learner7	Excel	2	06-27-2011 05:44 AM