#1
|
|||
|
|||
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! |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Quote:
I would use Alan's code, but with one modification of the first "If" statement: If Range("C" & i) = "" And Range("A" & i) <> "" Then |
#4
|
||||
|
||||
@Magpower
Hi and welcome please do not quote entire posts unnecessarily, they are just clutter. Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
visual sample
here's a visual sample of what rows should be removed (highlighted in yellow)
|
#7
|
||||
|
||||
@chrisok
Please post a sample sheet instead of useless images ( they also clutter the thread) Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#8
|
||||
|
||||
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 |
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 |
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 |