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

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-26-2019, 03:36 PM
ChrisOK ChrisOK is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 10 IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2019
Advanced Beginner
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW
 
Join Date: Sep 2016
Posts: 34
ChrisOK is on a distinguished road
Question IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW

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!
Reply With Quote
  #2  
Old 04-26-2019, 07:09 PM
Alansidman's Avatar
Alansidman Alansidman is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 10 IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 30
Alansidman is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 04-28-2019, 02:30 AM
MagPower MagPower is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 10 IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2019
Novice
 
Join Date: Feb 2019
Posts: 1
MagPower is on a distinguished road
Default

Quote:
Originally Posted by Alansidman View Post
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
Reply With Quote
  #4  
Old 04-28-2019, 02:56 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 7 64bit IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@Magpower
Hi and welcome
please do not quote entire posts unnecessarily, they are just clutter.
Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 05-04-2019, 06:41 PM
ChrisOK ChrisOK is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 10 IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2019
Advanced Beginner
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW
 
Join Date: Sep 2016
Posts: 34
ChrisOK is on a distinguished road
Question Throwing error - this line is highlighted

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?
Attached Images
File Type: jpg error-value.JPG (34.5 KB, 9 views)
Reply With Quote
  #6  
Old 05-04-2019, 06:48 PM
ChrisOK ChrisOK is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 10 IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2019
Advanced Beginner
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW
 
Join Date: Sep 2016
Posts: 34
ChrisOK is on a distinguished road
Default visual sample

here's a visual sample of what rows should be removed (highlighted in yellow)
Attached Images
File Type: jpg error-value-sample.JPG (57.6 KB, 9 views)
Reply With Quote
  #7  
Old 05-04-2019, 11:20 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 7 64bit IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@chrisok
Please post a sample sheet instead of useless images ( they also clutter the thread)
Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #8  
Old 05-05-2019, 09:00 PM
Alansidman's Avatar
Alansidman Alansidman is offline IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Windows 10 IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 30
Alansidman is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW Fill blank cell with value from adjacent cell kevinbradley57 Excel Programming 2 04-17-2018 08:40 AM
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW 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
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW 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


All times are GMT -7. The time now is 05:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft