Help with Autofilter and "Field: nn"

[jtma67]

I have a worksheet that I am using a macro with multiple Autofilter statements to select specific criteria as I create a summary report. One such statement I am using in the macro is:

Selection.AutoFilter field:=31, Criteria1:="Y", Operator:=xlOr, Criteria2:="N"

My problem is if I add columns anywhere prior to column 31, my macro will return incorrect results...unless of course I appropriately update the "field: =nn" number to correct the offset.

Is there anyway to that I can modify the "field:=" parameter to make it update automatically?

I read an article that discussed a similar situation with VLOOKUP where the column index reference could automatically update.... that code looked like:

=VLOOKUP(C1,A1:B12,COLUMN(B)-COLUMN(A1)+1)

However I couldn't get the COLUMN function to work. Thanks for any help.

 

[masim]

hi

i was once trapped in a similar situation and i tried something like this.

assumption: row one contains headings.


Sub Button1_Click()

Dim col As Integer

Rows(1).Select
Selection.Find(What:="your Header name of column 31", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

col = ActiveCell.Column

Selection.AutoFilter Field:=col, Criteria1:="Y", Operator:=xlOr, Criteria2:="N"

End Sub

you may need to modilfy the code to suit your needs but the idea i used was to search for the header name and get the column number returned and then apply the filter on the column number.

Regards
Asim