CODEWITHSUNDEEP
excelvba
rebecca sue alexander
rebecca sue alexander

Reputation: 25

filter column from a list on another sheet

Trying to filter column D on sheet1 with information in column A on sheet "Lg" but nothing seems to happen. I don't even get an error

Sub Filter()
'
' Filter Macro
'

'
    Columns("D:D").Select 'in Sheet 1
    Range("C1:C636").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Lg").Columns("A:A"), Unique:=False
End Sub

Upvotes: 1

Views: 633

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

Advanced Filter Example (2 Worksheets)

Before

enter image description here

After

enter image description here

  • It is assumed that your data starts in cell A1 and CurrentRegion works for referencing the complete range.
  • The titles (headers) of the destination criteria column D on worksheet Sheet1 and of the source criteria column A on worksheet Lg need to be the same.
Option Explicit

Sub TestAdvancedFilter()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Source (contains filter values)
    Dim sws As Worksheet: Set sws = wb.Worksheets("Lg")
    Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, "A").End(xlUp).Row
    If slRow = 1 Then Exit Sub ' no data
    Dim srg As Range: Set srg = sws.Range("A1:A" & slRow)
    
    ' Destination (is being filtered)
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1")
    If dws.FilterMode Then dws.ShowAllData
    Dim drg As Range: Set drg = dws.Range("A1").CurrentRegion
    
    drg.AdvancedFilter xlFilterInPlace, srg

End Sub

Upvotes: 1

Related Questions