Code for Row Deleting based on criteria in Column

[truitt20]

OK - so I am trying to use this code for my problem:

Sub DeleteSomeRowsPlease()
Dim ws As Worksheet, rngFilter As Range, rngDel As Range
Set ws = Sheets("Sheet1") 'set as desired
On Error Resume Next 'for SpecialCells
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set rngFilter = ws.Range("Q1:R" & ws.Cells(ws.Rows.Count, 18).End(xlUp).Row)
Set rngDel = ws.Range("Q2:R" & ws.Cells(ws.Rows.Count, 18).End(xlUp).Row)
ws.AutoFilterMode = False
rngFilter.AutoFilter field:=32, Criteria1:="CD"
rngFilter.AutoFilter field:=32, Criteria1:="SUPM"
rngDel.SpecialCells(xlCellTypeVisible).EntireRow.Delete
ws.AutoFilterMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Isn't working. My sheet has columns from A to CZ and a varying length of rows that is updated every minute. The column my criteria is in is column "AF" and I would like to delete rows that have a value of "CD" and "SUPM" in column AF. Or conversely delete every row that DOES NOT have "JAPANI" as data in column AF. Any help would be much appreciated.

thanks

jt

 

[AnotherHiggins]

How about something like this:

Cells.AutoFilter
Selection.AutoFilter field:=32, Criteria1:="<>*JAPANI*"
Rows("2:" & _
ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Sheets(1).AutoFilterMode = False

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Jt, why can you not stick to one thread? This is the third one I've seen on this issue ...

http://www.tek-tips.com/viewthread.cfm?qid=1267659

http://www.tek-tips.com/viewthread.cfm?qid=1268746

One thread per subject is less confusing..

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci