PDA

View Full Version : [SOLVED] Delete rows based on specific data



agnesz
12-17-2009, 01:44 PM
I have a file that I need to clean up based on certain criteria:

1. Delete rows that show any number other than “3” in column U “Region”.
2. Delete rows showing 0 in column “K” TktPrice.
3. Enter a "4" in any row that shows 0.01 in column “k”.
4. Enter a "5" in column V whenever the value in column "u" is not 97.
does this make sense??? I keep doing all these things manually and its driving me bananas. Any help would be much appreciated. I'm attachign the file below. You guys rock!

mbarron
12-17-2009, 02:11 PM
There was no attachment....

I wasn't sure what you wanted to do if the K column was 0.01, so I just made the macro change the 0.01 to 4.


Sub agnesz()
Dim i As Long, lRow As Long, bDel As Boolean
Application.ScreenUpdating = False
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lRow To 2 Step -1 'assums header row in row 1
If Range("U" & i) = 3 Or Range("K" & i) = 0 Then
bDel = True
End If
If Range("K" & i) = 0.01 Then Range("K" & i) = 4
If Range("U" & i) <> 97 Then Range("V" & i) = 5
If bDel = True Then Range("a" & i).EntireRow.Delete
bDel = False
Next
Application.ScreenUpdating = True
End Sub

agnesz
12-17-2009, 03:08 PM
sorry about the attachment not coming through... this is perfect. I forgot one more condition though.

If value in column "k" is not 0.01, but the value in column "q" is 4, then the "q" value should be equal/changed to 3.

Does that make sense?

Thanks so much

Aussiebear
12-17-2009, 03:36 PM
Try adding this line

IF Range("K" & i) <> 0.01 AND Range("Q" & i) = 4 Then Range("Q" & i) = 3

below the following line:

IF Range ("U" & i)<>97 Then Range("V" & i)= 5