Row Delete
 

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

Row Delete
 

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
-------------------



--
Message posted from http://www.ExcelForum.com

Row Delete
 

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)?

--
Regards,
Tom Ogilvy



"kkknie " wrote in message
...
One problem is that if the lines to be deleted come right after each
other, the second one will not get deleted. This is because when you
delete the row and increment the y variable, it skips the row after the
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
Loop
--------------------

K


---
Message posted from http://www.ExcelForum.com/

Row Delete
 

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.
Do while y<4
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





"Doug Van" wrote in message
...
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

Row Delete
 

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



--
Message posted from http://www.ExcelForum.com

Row Delete
 

Thanks for all the input.

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



"Paulw2k" wrote in message
...
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.
Do while y<4
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





"Doug Van" wrote in message
...
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