CODEWITHSUNDEEP
vbaexcel
Michael J
Michael J

Reputation: 33

Remove lines IF AND THEN ELSEIF

Everyone!

I have a heavy file with different values in A and D columns. Now I want this script to complete the following operation:

  1. If value in column A is "Option One" and "K" in column D, then remove this entire line.
  2. Additionally Remove entire raw if value in column A is "Option Two" and "M" in column D

The following code performs this operation for 70% and I can't find the issue. It always leaves behind some rows and to completely remove unwanted entries, I have to run this code twice.

Will appreciate your help! Thank You!

Sub RemoveSomeLines()

Dim RemoveRow As Long

RemoveRow = Cells(Rows.Count, "A").End(xlUp).Row
For y = 1 To RemoveRow
If Cells(y, "A").Value = "Option One" And _
   Cells(y, "D").Value = "K" Then
   Cells(y, "A").EntireRow.Delete

ElseIf Cells(y, "A").Value = "Option Two" And _
   Cells(y, "D").Value = "M" Then
   Cells(y, "A").EntireRow.Delete

End If
Next y

End Sub

Upvotes: 1

Views: 74

Answers (1)

Jason Clement
Jason Clement

Reputation: 233

Try going backwards:

For y = RemoveRow To 1 Step -1

It is probably skipping rows because you are removing a row, moving the next row up, and then skipping the row that was moved up on the next loop because y increments by 1.

It looks like this:

Remove row 20.
Entire sheet shifts upward.
Row 21 is now row 20.
y increments on the next loop to become 21.
The new row 20 was actually skipped entirely.

Alternatively, I think it would work if you decremented y by 1 when you removed a row, but it's less code changes just to work backward instead of forward.

Upvotes: 4

Related Questions