CODEWITHSUNDEEP
vbaexcel
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Filter sheet data based on a column value exists in another sheet

Ive a sheet with data similar to this

Users  Role
-----------
Name1   a
Name2   b
Name3   c
Name4   d
Name5   e
Name6   f
Name7   g

Another Sheet

Users Req
------
Name4
Name6
Name7

So the sheet data should be filtered based on the column values exists in second sheet. So after filtering the first sheet looks like this.

Users   Role
-------------
Name4    d
Name6    f
Name7    g

How can we achieve like this

Upvotes: 0

Views: 429

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Save the values in the Other sheet in an array, in my code below it's FilterArr, and then you can AutoFilter according to the values in this array by using Criteria1:=FilterArr.

More explanations inside the code below as comments:

Code

Option Explicit

Sub FilterAccordingtoValuesInOtherSheet()

Dim Sht1 As Worksheet, sht2 As Worksheet
Dim FilterArr As Variant
Dim LastRow As Long

Set Sht1 = ThisWorkbook.Sheets("Sheet1") ' modify to your sheet's name where you have the data you want to filer
Set sht2 = ThisWorkbook.Sheets("Sheet2") ' modify to your sheet's name where you have values you want to use for the filter

With sht2
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' get last row with data in column A

    FilterArr = Application.Transpose(.Range("A2:A" & LastRow).Value2)  ' get the values from the second sheet to an array
End With

With Sht1
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' get last row with data in column A

    ' set the Auto-Filter by column A, to the values in the array
    .Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:=FilterArr, Operator:=xlFilterValues
End With

End Sub

Upvotes: 4

Related Questions