Excel Macro: Filter a column to return up to 5 values

dabug80
dabug80 used Ask the Experts™
on
Hi,

Seeking help on an event macro that will automatically filter a table range when a cell changes.

I would like the filter to return results for up to 5 record types (within the same column - column A).

I have already attempted a macro within the sample spreadsheet.

Further instructions on the lookup sheet.

thanks
ee-Calculatorv35.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

pls try (F19 is changing, only the value of "lumoeligibleelec" is changing not the formula)

Private Sub Worksheet_Change(ByVal Target As Range)
' This will change the filter on the lookup sheet
If Not (Intersect(Target, Range("F19")) Is Nothing) Then
    aValues = WorksheetFunction.Transpose(Range("lumoeligibleelec"))
    Range("lumoelecplans").AutoFilter Field:=1, Criteria1:=aValues, Operator:=xlFilterValues
End If
   
End Sub

Open in new window

Regards
Jon von der HeydenCourse Leader & Managing Director

Commented:
Hi

I have created a dynamic named range.

Name: drng_PlanCodes_Filter
RefersTo: =Lookup!$B$32:INDEX(Lookup!$B$32:$B$36,MATCH(REPT("z",255),Lookup!$B$32:$B$36,1),1)

Then the code that I have used:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngTemp         As Excel.Range
    
    Const strValuesDNR  As String = "drng_PlanCodes_Filter"
    Const strTableName  As String = "lumoelecplans"
    With Me
        If Not Application.Intersect(.Range("F19"), Target) Is Nothing Then 'check if F19 has changed
            If Len(.Range("F19").Value) > 0 Then 'it is not blank
                On Error Resume Next
                    Set rngTemp = Evaluate(ThisWorkbook.Names(strValuesDNR).RefersTo) 'check that there are filtervalues
                On Error GoTo 0
                If Not rngTemp Is Nothing Then 'don't filter if there are no filter values
                    Call FilterArray(.Range(strTableName), 1, Application.Transpose(rngTemp.Value)) 'apply the filter
                End If
            End If
        End If
    End With
End Sub

Public Sub FilterArray(ByVal rngTable As Excel.Range, ByVal lngField As Long, ByVal varValues As Variant)
    With rngTable
        Call .AutoFilter(Field:=lngField, Criteria1:=varValues, Operator:=xlFilterValues)
    End With
End Sub

Open in new window

You can accomplish this simple as this.

Private Sub Worksheet_Change(ByVal Target As Range)
 
        If Not Application.Intersect(Target, Range("F19")) Is Nothing Then
       Call filter
       End If
       
End Sub

Sub filter()

Application.ScreenUpdating = False
If ActiveSheet.FilterMode = True Then
     ActiveSheet.ShowAllData
     End If
ActiveSheet.Range("A47").AutoFilter Field:=1, Criteria1:=Array(Range("B32").Value, Range("B33").Value, Range("B34").Value, Range("B35").Value, Range("B36").Value), Operator:=xlFilterValues

  Application.ScreenUpdating = True
End Sub

Open in new window



Please see attached.
ee-Calculatorv35.xlsm

Author

Commented:
Thanks. I'm sure all answers worked (thanks all experts), but this one was easy to check as the workbook was attached. I tried this one first and all was goo.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial