VBA Macro to Autofilter based on Sheet 2 list.

Bluesguy07

New Member
Joined
Mar 11, 2016
Messages
28
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top