Advanced Filter & Sort - Heeyoung-Ahn/Excel_VBA GitHub Wiki

Range.AdvancedFilter (Action, CriteriaRange, CopyToRange, Unique)

Sub AF()
    Dim rngDB As Range
    Dim rngCriteria As Range
    Dim rngCopy As Range
    
    With Sheets("data")
        Set rngDB = .Range("A1").CurrentRegion
        Set rngCriteria = .Range("K1").CurrentRegion
        Set rngCopy = .Range("N1").CurrentRegion.Resize(1)
    End With
    
    rngDB.AdvancedFilter xlFilterCopy, rngCriteria, rngCopy, False
End Sub

주의할점

  • 같은 행에 작성된 조건은 And, 다른 행에 작성된 조건은 Or
  • 비교연산자 사용 시: <>100, <>불합격, >=90, 와일드카드 사용 가능

Sort

Sub SortSample()
    Sheet1.Activate '정렬할 시트 선택
    ActiveSheet.AutoFilterMode = False 'AutoFilterMode = False: 자동필터 해제, FilterMode = False : 필터링된 내용 해제
    Cells(1, 1).AutoFilter
    With ActiveSheet.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Cells(1, 1), CustomOrder:="본부장,부장,과장,대리,사원"
        .SortFields.Add Key:=Cells(1, 2), Order:=xlDescending
        .SortFields.Add Key:=Cells(1, 3), Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
    ActiveSheet.AutoFilterMode = False 
End Sub
  • If the AutoFilter drop-down arrows are visible but no rows are currently filtered (all rows are visible), the AutoFilterMode property is True and the FilterMode property is False.

⚠️ **GitHub.com Fallback** ⚠️