Custom & Auto Filter Quagmire

[Mizzness]

All.

In my spreadsheet, I am filtering by one criteria & having all rows deleted once they have been identified.
Upon completion, I have a custom filter based on two sets of criteria that should show me the data for "Bond" & "Repo".

One issue I have found is that I need to manually add the auto filter before the first macro can fire.
1. Can someone assist on this so know manual intervention is needed ?
2. The second macro doesn't give me the data I require even after using the recorder.
3. Can the macros be combined ?

Auto Filter Code:

Sub Caesar()

With Sheets("sens data").Columns("AJ:AJ").Rows("2:13000")
.AutoFilter Field:=36, Criteria1:="ERASE"
.EntireRow.Delete Shift:=xlDown
End With
End Sub

Custom Filter Code:
Sub Brutus()

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="<>Bond*", Operator:=xlAnd, _
Criteria2:="<>Repo*"
Range("A1").Select
End Sub

Thanx.

 

[SkipVought]

Hi,

Check out the AutoFilterMode.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[ck1999]

your second filter shows rows that do not start with "bond* and do not start with "repo"

If you want all the records that column F starts with either "bond" or "repo" then change the code to this


Selection.AutoFilter Field:=6, Criteria1:="=Bond*", Operator:=xlOr, _
Criteria2:="=Repo*"

ck1999

 

[Mizzness]

ck 1999,

You're right but I mistyped.
I wanted data shown that was not Repo or Bond related.

Mizzness

 

[SkipVought]

"I wanted data shown that was not Repo or Bond related."

There's nuthin' at all stopping your from achieving this. Use you macro recorder if necessary.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[ck1999]

1st

One issue I have found is that I need to manually add the auto filter before the first macro can fire.

use this
Sub Caesar()

With Sheets("sens data").Columns("AJ:AJ").Rows("2:13000")
.autofilter
.AutoFilter Field:=36, Criteria1:="ERASE"
.EntireRow.Delete Shift:=xlDown
End With
End Sub


Make sure there is no autofilter on either worksheets before running the codes.

ck1999

 

[SkipVought]

"Make sure there is no autofilter on either worksheets before running the codes."

That's what AutoFilterMode is for.



Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}