Help with Autofilter and "Field: nn"

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Help with Autofilter and "Field: nn"

  1. #1
    New Member
    Join Date
    Dec 2003
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with Autofilter and "Field: nn"

     
    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:

    Code:
    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:

    Code:
    =VLOOKUP(C1,A1:B12,COLUMN(B)-COLUMN(A1)+1)
    However I couldn't get the COLUMN function to work. Thanks for any help.

  2. #2
    Board Regular
    Join Date
    Jun 2003
    Location
    Pakistan
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Autofilter and "Field: nn"

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com