AutoFilter Field # - How do numeric designations work?

[hip2b2]

I am using the following code, works just fine. My question has to do with the field designation.

 .AutoFilter Field:=11, Criteria1:="=Closed" & strName & "*", Operator:=xlAnd
 .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
 .AutoFilter Field:=11

The field that contains the filter criteria is Col S. As S is not the 11th letter of the alphabet how does this work? I do note that Col S is the 11th Col in my spreadsheet that is populated with text, or am I drawing a false conclusion?

Is there any simple way to modify the code to relate to an absolute column designation?

Thanks

hip

 

[Fluff]

It is the column index, where the first filtered column is 1. So 11 is the 11th column in the autofilter, which suggests your data starts in col I

 

[hip2b2]

It is the column index, where the first filtered column is 1. So 11 is the 11th column in the autofilter, which suggests your data starts in col I

Well that makes more sense that what I was supposing.

While there is data in Col I it is definitly not the data that is being filtered.

 

[Fluff]

While there is data in Col I it is definitly not the data that is being filtered.

No but it sounds as though col I is the 1st column in the auto filter range.

 

[hip2b2]

Ahah!!!???? So to do what I should have done, here is the rest of the code:

Set h = Sheets("For XYZ")
If h.AutoFilterMode Then h.AutoFilterMode = False
lr = Columns("A:K").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With h.Range("A6:K" & lr)

.AutoFilter Field:=2, Criteria1:="=Open" & strName & "*", Operator:=xlAnd
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=2

.AutoFilter Field:=11, Criteria1:="=Closed" & strName & "*", Operator:=xlAnd
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=11

Not sure how to set the range properly

Thanks again

 

[Fluff]

You need to qualify the line getting the last row with the sheet like

lr = h.Columns("A:K").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[hip2b2]

lr = h.Columns("A:K").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[hip2b2]

Not quite sure what to do with this. I tried to change the range from A:K to A:S and the Field from 11 to 19 but the macro fails at that point.

 

[Fluff]

What is the range you want to filter & which columns?

 

[hip2b2]

Two ranges actually.

• One Range is Col S
• One Range is Col B