Macro to remove rows containing X

[kekstrom]

If Application.WorksheetFunction.CountIf(.Rows(lRow), "Bought") = 0 Then .Rows(lRow).delete
' Delete each row if the value "Bought" not exist in the row (It will look in the whole row)

Right now I am using this to remove any row that doesn't contain the word Bought in it.

The problem I have encountered is that some of the rows that contain Bought, also contain Cancelled, and they are still making it though the filter and causing problems.

I need to make it so that the row must contain both "Processed" and "Bought" in order to not be deleted, and if it contains "cancelled" and "bought", get rid of it.

Thanks!

 

[Weaver]

Do these values appear in different columns?

I'm thinking that done right, you could apply an autofilter to display only the rows you want deleted and then delete all visible rows. This sort of thing crops up a lot on this board and that's the way the usual solution goes.

By way of an example, this code deletes all rows where column B has a zero

Sub test()
    With Range("A1").CurrentRegion
        lr = .Rows.Count
        .AutoFilter
        .AutoFilter field:=2, Criteria1:="0"
        .Offset(1).Resize(lr - 1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[HalfAce]

The wording in your text has me wondering if I understand, but if I do then perhaps just adding another condition to delete would do.

If you change this line:

If Application.WorksheetFunction.CountIf(.Rows(lRow), "Bought") = 0 Then .Rows(lRow).Delete

to something like this:

If Application.WorksheetFunction.CountIf(.Rows(lRow), "Processed") = 0 And _
   Application.WorksheetFunction.CountIf(.Rows(lRow), "Bought") = 0 Then .Rows(lRow).Delete

then any row that does not contain the values "Bought" & "Processed" will be deleted.

(Do you need to specify the rows that actually DO contain "Cancelled" for some reason?)

Another consideration. Are you looping down through the rows of the sheet to run these tests? (If so you'll want to loop up from the bottom and not down from the top.)

Hope it helps.

 

[kekstrom]

Do these values appear in different columns?

Yes, the values are in different columns.

Here is the code I am using now:

Sub Remove_Sell_Cancel()
Dim lRow As Long
Dim calcmode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 9000

For lRow = EndRow To StartRow Step -1

If Application.WorksheetFunction.CountIf(.Rows(lRow), "Bought") = 0 Then .Rows(lRow).delete
' Delete each row if the value "Bought" not exist in the row (It will look in the whole row)




Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub

Halface, I tried the code you gave me and it still did not remove the Cancelled.

We are taking this data to analyze what we bought for the day. We only want to see what we bought, and not what we bought and then later cancelled, so we just want anything that isn't a processed buy to be removed.

Hope this makes some sense! THANKS

 

[HalfAce]

Halface, I tried the code you gave me and it still did not remove the Cancelled.

Yeah, the change I made makes it so any row(s) that contain both the values 'Processed' and 'Bought' get deleted.
So... the question is, what are all the criteria for having the row deleted?
If it contains 'Processed' and 'Bought', delete it. (yes?)
If it contains 'Bought', delete it. (yes?)
If it contains 'Cancelled', delete it. (yes?)

If those are all correct, are there any other criteria that would make you want to delete the row?

 

[kekstrom]

Yeah, the change I made makes it so any row(s) that contain both the values 'Processed' and 'Bought' get deleted.
So... the question is, what are all the criteria for having the row deleted?
If it contains 'Processed' and 'Bought', delete it. (yes?)
If it contains 'Bought', delete it. (yes?)
If it contains 'Cancelled', delete it. (yes?)

If those are all correct, are there any other criteria that would make you want to delete the row?

I want to keep the processed and bought.

Delete processed sold, delete cancelled bought. If it doesn't contain both processed and bought, I want it gone. Here is what a sample table might look like.

Processed	59V897779	T6WW9A	613344NW6			Bought	5,000.00	$100.008
Processed	59V897779	T6W7CJ	613344NW6			Sold	5,000.00	$100.008
Cancelled	59V897779	T6W00K	199491L90			Bought	5,000.00	$102.657
Processed	59V897779	T6W7A0	194635BN7			Sold	5,000.00	$100.727
Processed	59V897779	T6WWRW	194635BN7			Bought	5,000.00	$100.727
Processed	59V897779	T6W7A7	03588EV26	&		Sold	5,000.00	$103.41
Processed	59V897779	T6W1FU	03588EV26			Bought	5,000.00	$103.41

 

[HalfAce]

If it doesn't contain both processed and bought, I want it gone.

OK, how about we change up the logic being used to something like this:
Try replacing this line:

If Application.WorksheetFunction.CountIf(.Rows(lRow), "Processed") = 0 And _
   Application.WorksheetFunction.CountIf(.Rows(lRow), "Bought") = 0 Then .Rows(lRow).Delete

with this instead:

If Application.WorksheetFunction.CountIf(.Rows(lRow), "Processed") > 0 Then
   If Application.WorksheetFunction.CountIf(.Rows(lRow), "Bought") > 0 Then
   'Processed and Bought both exist so do nothing
Else
   'Either Processed or Bought (or both) do not exist so delete the row
   .Rows(lRow).Delete
End If

Does that help?

 

[Weaver]

Try this.

I'm assuming ""processed" will be in column 1 and "Bought" will be in 6

Sub test()
    With Range("A1").CurrentRegion
        lr = .Rows.Count
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<>Processed"
        .Offset(1).Resize(lr - 1).EntireRow.Delete
        .AutoFilter
        .AutoFilter Field:=6, Criteria1:="<>Bought"
        .Offset(1).Resize(lr - 1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[kekstrom]

Thanks Weaver, yours does the job!

Sorry Halface, weavers was the first I tried when I returned to check the thread!