Delete Row if multiple cells are blank

nguerra

New Member
Joined
Oct 1, 2013
Messages
46
I have a bunch of rows that I want to delete if the cell D, E, and F are blank. What I have below to identify those row's works just fine but if i try and Delete the Row, it doesn't work completely. How can I rewrite the Else statement to delete the row. I think what is happening is when the row is deleted the count becomes changed.


Dim lastRow As Long, i As Long

lastRow = Cells(Rows.Count, "D").End(xlUp).Row

For i = 2 To lastRow

If Range("D" & i) <> "" And Range("E" & i) <> "" And Range("F" & i) <> "" _
Then Range("A" & i) = "Good" _
Else Range("A" & i) = "Delete" ' If I change this to "Else Range("A" & i).EntireRow.Delete" I don't get all of the cells to delete

Next i

End Sub

Any help anyone can provide would be greatly appreciated.
 

Excel Facts

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,535
Office Version
  1. 365
Platform
  1. Windows
When deleting/inserting rows it's best to loop bottom up
VBA Code:
For i = LastRow To 2 Step -1

If Range("D" & i) <> "" And Range("E" & i) <> "" And Range("F" & i) <> "" Then
   Range("A" & i) = "Good"
Else
   Rows(i).Delete
End If

Next i
 

nguerra

New Member
Joined
Oct 1, 2013
Messages
46
Thanks Fluff, worked just fine and makes sense now by going backwards. Truly appreciate your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,535
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,687
Members
416,935
Latest member
Atulcp

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