Delete Rows Based on Inforamtion

[jasonwooten]

I ahve a spreadsheet that I need some help with the code please. I have PO numbers in column P, and in coulmn X I have pulled the first 2 letters out with =LEFT(X3,2).
I am wanting to delete rows of information based on the contents in X.
the range of cells is X3:X1000
I want to delete rows if they equal OJ,OK,OS,FR,BD

This is the code that I have but it does not work.
(by the way the yes I know the first part of the code sorts the contents)

Sheet1.Range("x3:x1000").Name = "n"
Application.ScreenUpdating = False


Rows("3:2000").Select
Selection.Sort Key1:=Range("N3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Activate
Range("I3:I1000").Name = "n"

If n = OJ Then
Delete.EntireRow
End If
If n = OJ Then
Delete.EntireRow
End If
If n = OK Then
Delete.EntireRow
End If
If n = FR Then
Delete.EntireRow
End If
If n = BD Then
Delete.EntireRow
End If
If n = DF Then
Delete.EntireRow
End If

Application.ScreenUpdating = True








End Sub

 

[jdhilljr]

Try this


Sub test()
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "OJ" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "OK" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "OS" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "FR" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "BD" Then Rows(z).Delete
Next z
End Sub


Jim

 

[jasonwooten]

Jim,
can you help me out with something, what dies the 24 stand for in this. I pasted this code in and it doesn not work.
Can you help. Do I need to name the range with z. Like Reports.Range("x3:x1000").Name = "z" for this to work.This is what I have, it sorts the PO # first then I want it to delete if coulmn X equals one of the the values.

Reports.Range("x3:x1000").Name = "z"
Application.ScreenUpdating = False


Rows("3:2000").Select
Selection.Sort Key1:=Range("N3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Row = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "OJ" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "OK" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "OS" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "FR" Then Rows(z).Delete
Next z
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 24).Value = "BD" Then Rows(z).Delete
Next z


Application.ScreenUpdating = True


Thanks Jim,


Jason

 

[000Steve000]

I haven't tested it but doesn't your original code need some speech marks around the strings?
eg
If n = "DF" Then
Delete.EntireRow


The Cells(x,y) function refers to cells(row,column) so 24 is the 24th Column "X

 

[jdhilljr]

Run this as a separate macro.
the x column is the 24th.
z is a variable that counts the rows.
This runs from the bottom up.

Jim

 

[jasonwooten]

Jim this is still not working.
I went back and pasted your code into a new macro.
But nothing happens...

Can you help..

 

[KenWright]

Try this

Sub DelRowsWhichContain()

Dim r As Long
Dim LastRow As Long

Application.ScreenUpdating = False

LastRow = 1000
For r = LastRow To 3 Step -1
   If StrComp(Cells(r, "X"), "OJ", vbBinaryCompare) = 0 _
      Or StrComp(Cells(r, "X"), "OK", vbBinaryCompare) = 0 _
      Or StrComp(Cells(r, "X"), "OS", vbBinaryCompare) = 0 _
      Or StrComp(Cells(r, "X"), "FR", vbBinaryCompare) = 0 _
      Or StrComp(Cells(r, "X"), "BD", vbBinaryCompare) = 0 Then
   Rows(r).Delete
   Else: End If
Next r

Application.ScreenUpdating = True

End Sub

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------