Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA Macro to Autofilter based on Sheet 2 list.

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Macro to Autofilter based on Sheet 2 list.

    Hello all! My apologies if this has already been covered, but I am unable to find how to do what I'm looking for (both here and youtube).

    I'm looking to write a VBA script that will autofilter based on a single column on Sheet 2.

    Basically

    Sheet 1 = Filter Column Y
    Sheet 2 = Column A contains the list

    The list can be up to 35 unique values.

    I don't see this as being something over complicated, I just can't seem to figure it out.

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    UK
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Hello there. It may seem simple, but autofilter is a pain when you want more than 2 criteria. The answer is to use an array holding your desired values - this is a quick attempt at it - it allows for 5000 values - you will want to tweak it I think:
    Code:
    Sub InclusiveFilter()
    Dim IncludeArray(5000) As String
    With Sheets("Sheet2")
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' assumes a header row so start in row 2
    For i = 2 To lastrow
    IncludeArray(i - 2) = .Range("A" & Format(i)).Text
    Next i
    End With
    
    
    With Sheets("Sheet1")
    ' assumes the filtered area is named table1
        .Range("table1").AutoFilter Field:=25, Criteria1:=IncludeArray, Operator:=xlFilterValues
    End With
    End Sub
    Last edited by jmacleary; Jun 14th, 2018 at 04:45 AM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Slightly different
    Code:
    Sub AryFilter()
       Dim Ary As Variant
       With Sheets("Sheet2")
          Ary = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
       End With
       With Sheets("Sheet1")
          If .AutoFilterMode Then .AutoFilterMode = False
          .Range("1:1").AutoFilter 25, Ary, xlFilterValues
       End With
    End Sub
    Assumes header in row 1
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Thanks jmacleary. I'm getting a "Variable not defined" error on the following line

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

    I'm going to give the one Fluff suggested a shot now.

    Fluff's is not working for me either. I'll toy around with it later and try to get a better understanding. I have another spreadsheet that uses a single variable when doing autofilter, which is why I thought it wouldn't be too difficult.
    Last edited by Bluesguy07; Jun 14th, 2018 at 08:47 AM. Reason: Addied Text

  5. #5
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    UK
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    If you are running it in a module with option explicit set, then all the variables I have used will need defining. Add
    Code:
    Dim lastrow as Long
    Dim i as long
    and make sure you have table1 set as the name of the filtered range.

  6. #6
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    So, here's the other option since I can't get either of the two above to work properly.

    I don't mind doing an advanced filter. however, I'd still want to do a record macro. When doing an advanced filter, it does not like the blanks. Is there a way to do an advanced filter where it will search for anything in column Y, but not include blanks?

  7. #7
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Quote Originally Posted by jmacleary View Post
    If you are running it in a module with option explicit set, then all the variables I have used will need defining. Add
    Code:
    Dim lastrow as Long
    Dim i as long
    and make sure you have table1 set as the name of the filtered range.
    Ok. I will give that a shot once I get some more time today. If this works, I'll kiss you.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    In what way do the 2 macros not work?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Quote Originally Posted by Fluff View Post
    In what way do the 2 macros not work?
    I think it's something I'm doing, not necessarily the code. I work with macros so infrequently, that there may be something basic I'm forgetting or just missing in general.

  10. #10
    New Member
    Join Date
    Mar 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    So, using the code from jma below (I changed it to Field:=13 as it's actually column M, not Y that I need to filter. My mistake. I also changed Table1 to List1 as that's what I have the table named.

    Code:
    Sub InclusiveFilter()
    Dim IncludeArray(5000) As String
    With Sheets("Sheet2")
    Dim lastrow As Long
    Dim i As Long
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' assumes a header row so start in row 2
    For i = 2 To lastrow
    IncludeArray(i - 2) = .Range("A" & Format(i)).Text
    Next i
    End With
    
    
    
    
    With Sheets("Sheet1")
    ' assumes the filtered area is named table1
        .Range("List1").AutoFilter Field:=13, Criteria1:=IncludeArray, Operator:=xlFilterValues
    End With
    End Sub
    After defining the variables, the fliter works. However, is there a way to have it filter out the blanks? For perspective, this spreadsheet has 20k rows, with over half of them being blank in the column I need to filter.

Some videos you may like

User Tag List

Tags for this Thread

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
  •