Deleting Rows based on criteria in Column

[truitt20]

Is there a way to delete whole rows based on criteria contained within the row/column? For example:

Col1 Col2 Col3
23 rt cd
56 gh cd
21 df jp


I would want to delete rows 1 & 2 b/c col3 did not have "jp" in the field, but keep row 3 b/c data in column 3 did have "jp" populated in column.

thanks

 

[RonRepp]

Try this:

Range("A1").Activate

Do
  If ActiveCell.Value="JP" Then
      ActiveCell.EntireRow.Delete (xlUp)
  Else
      Activecell.Offset(0,1).Activate
  End if
Loop

I hope this helps

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[RonRepp]

Sorry:

It should be:

Range("A1").Activate

Do
  If ActiveCell.Offset(0,2).Value="JP" Then
      ActiveCell.EntireRow.Delete (xlUp)
  Else
      Activecell.Offset(1,0).Activate
  End if
Loop

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[firefytr]

Rather than activate or use the ActiveCell object (not a good idea) check out this link:

http://www.mrexcel.com/board2/viewtopic.php?t=228402

Regards,
Zack Barresse

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

 

[xlbo]

The best way to do conditional deleting is to use the autofilter.

Filter for the criteria you want to delete.
Delete all visible cells.
Then remove autofilter

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

That's what the link does Geoff. ;-)

Regards,
Zack Barresse

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

 

[xlbo]

yeh but no but yeh but no but <instant excuse maker>I didn't want to send people to a rival forum</instant excuse> ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

LOL! I'll keep that in mind the next time.

Regards,
Zack Barresse

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

 

[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

 

[xlbo]

you must change:

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"

to be relevant to YOUR data layout

You also need to look at the help file for the autofilter method which will show you how to use 2 criteria and an operator to join them

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Geoff, don't think the field would be 32, it should be relevant to the range [column] being filtered, so Q should be field 1 and R should be field 2.

Regards,
Zack Barresse

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

 

[xlbo]

Zack - if you look - that is what they were in the code that truitt20 posted. I was merely highlighting what bits would need to be changed as it would seem that it is not columns Q:R that need to be filtered

The column my criteria is in is column "AF"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Ah, I'm not sure I understand the data structure here. truitt20 has this topic posted in numerous positions/threads and it's becoming a hassle to keep track of which is which..

Regards,
Zack Barresse

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