[VBA]엑셀(Excel) 특정 셀의 행의 값들 기반으로 다른 시트에서 자동 필터링하기

1. 클릭 타켓으로 삼을 셀값 E2:E3000 

2.  타켓의 좌/우 값을 저장(필터링 값)

3. Dictionary 객체 사용

 

 

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim dict As Object

Dim category As Range
Dim clsfication As Range
Dim week As Range

Dim var As Variant
Dim var2 As Variant
Dim var3 As Variant

Dim cl As Variant

Dim arri As Integer

If Intersect(Target, Range("E2:E3000")) Is Nothing Then Exit Sub ' 영역변수 Target과 E열의 교차되는 교집합 영역이 없다면 E2셀에서 프로시저 발동
    If Target.Cells.Count > 1 Then Exit Sub                         ' Target의 셀이 1개 이상일 경우
    Worksheets("DATA").AutoFilterMode = False

    Set category = Range(Target.Address, Range(Target.Address).End(xlToRight))
    Set clsfication = Range(Target.Address).Offset(0, -2)
    Set week = Range(Target.Address).End(xlToLeft)
    
    var = category.Value
    var2 = clsfication.Value
    var3 = week.Value
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cl In var
        
        If Len(Trim(cl)) = 0 Then
            
            Exit For
        ElseIf Not cl = Target.Value Then
            dict(cl) = cl
        End If
        
    Next
    
    If IsEmpty(Target) Then Exit Sub

Set srch = Worksheets("DATA").Range("I1")
    If Not srch Is Nothing Then
        Application.GoTo srch
        
     
        For arri = 0 To dict.Count - 1
          
          With Worksheets("DATA").Range("I2")
          
              .AutoFilter Field:=arri + 9, Criteria1:=dict.items()(arri), Operator:=xlFilterValues      '  ActiveSheet.배열에 있는 값을 필터링함
              
          End With
          
          Cancel = True
          
        Next
        
        ActiveSheet.Range("A1").Select
        
        With Worksheets("DATA").Range("D2")
            .AutoFilter Field:=4, Criteria1:=var2
            .AutoFilter Field:=1, Criteria1:=var3
        End With
        
        ActiveSheet.Range("A1").Select
        
        
        ActiveSheet.Columns("A:D").Hidden = False
        ActiveSheet.Columns("G:N").Hidden = False
        
        ActiveSheet.Range("A1").Select
        
    End If
End Sub

참고

dict문법 / https://excelmacromastery.com/vba-dictionary/ 

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery