Row Delete

[Doug Van]

This is similar to the "Is this Possible?" post.

I want to delete a record that meets a certain condition.
This works with the first condition, but anything after the "or" won't
delete.

y = 5
Do While Range("A" & y) <> ""
If Range("F" & y).Value = "957-TYPE" Or _
Range("F" & y).Value = "149-DUPLICATE" Then
Range("F" & y).EntireRow.Delete
End If
y = y + 1
Loop

 

[kkknie]

One problem is that if the lines to be deleted come right after eac
other, the second one will not get deleted. This is because when yo
delete the row and increment the y variable, it skips the row after th
one you deleted (because it moves up one).

The fix should be:


Code
-------------------
y = 5
Do While Range("A" & y) <> ""
If Range("F" & y).Value = "957-TYPE" Or _
Range("F" & y).Value = "149-DUPLICATE" Then
Range("F" & y).EntireRow.Delete
y = y - 1
End If
y = y + 1
Loo

 

[Tom Ogilvy]

Another approach:

y = 5
Do While Range("A" & y) <> ""
If Range("F" & y).Value = "957-TYPE" Or _
Range("F" & y).Value = "149-DUPLICATE" Then
Range("F" & y).EntireRow.Delete
Else
y = y + 1
End if
Loop

Your conditional test requires an exact match. Are the entries an exact
match (no trailing spaces or anthing like that)?

 

[Paulw2k]

Hi Doug,

If you are going to use this method for row deletion then work up from the
bottom.
Everything under a deleted row moves up one, so those rows should be ones
the code has already tested.

So,
ascertain the last block row number with a bit of code.


y=Range("A" & Rows.Count).End(xlUp).Row 'Assumes nothing beneath the
block of data you are dealing with.

Range("F" & y).Value = "149-DUPLICATE" Then
Range("F" & y).EntireRow.Delete
End If
y = y - 1

Loop

 

[kkknie]

Tom asks a good question about the trailing/leading spaces. If ther
are any, change the If statement to:

If Trim(Range("F" & y).Value) = "957-TYPE" Or _
Trim(Range("F" & y).Value) = "149-DUPLICATE" Then

Also, the comparison is case dependant, so if something may have gon
in lowercase, change to:

If UCase(Trim(Range("F" & y).Value)) = "957-TYPE" Or _
UCase(Trim(Range("F" & y).Value)) = "149-DUPLICATE" Then

 

[Doug Van]

Thanks for all the input.

I think it is better starting from the bottom, I did notice it was deleting
all the rows.