VBA Macro to Autofilter based on Sheet 2 list.

[Bluesguy07]

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.

 

[Bluesguy07]

Ok, so I think we're good. Instead of worrying about filtering out blanks, I just added a delete rows command.

Sub DeleteRows()    Range("M:M").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Bluesguy07]

Duplicate

 

[jmacleary]

My apologies. My code as written will have included blanks as the array had too many elements. This modified code will only set up the array with the correct number of elements:

Sub InclusiveFilter()Dim IncludeArray() As String
With Sheets("Sheet2")
Dim lastrow As Long
Dim i As Long
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
ReDim Preserve IncludeArray(lastrow - 2)
' 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

 

[Bluesguy07]

My apologies. My code as written will have included blanks as the array had too many elements. This modified code will only set up the array with the correct number of elements:

Sub InclusiveFilter()Dim IncludeArray() As String
With Sheets("Sheet2")
Dim lastrow As Long
Dim i As Long
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
ReDim Preserve IncludeArray(lastrow - 2)
' 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

Thanks! This works perfectly! I'd rather filter out blanks than have to delete content anyways. I appreciate everything you heroes of the excel world do. I'd mark the thread as solved, but I am unsure how to do that.