Microsoft Excel에서 VBA를 이용하여 여과 한 후 자동 필터 기준을 표시

Option Explicit

Sub FilterCriteria()

'Declaring variables

Dim IntRow, IntCol As Integer

Dim MainString, StringValue As Variant

'Initializing the row and column number

IntRow = Range("A10").CurrentRegion.Rows.Count + 12

IntCol = 1

'Looping through all the cells until blank cell is encountered in the 10th row

Do Until IsEmpty(Cells(10, IntCol))



With ActiveSheet.AutoFilter.Filters(IntCol)

'Checking whether filter is applied on the column

If .On Then



MainString = "Filter On Column no. " & IntCol & " on values : "



'Creating text which consists of values used in the filter

For Each StringValue In .Criteria1

MainString = MainString + Mid(StringValue, 2) + "|"

Next



'Assigning value to cell

Cells(IntRow, 1).Value = MainString



Exit Do



End If



End With



IntCol = IntCol + 1

Loop

'Copying the visible cells to row after the filter data

Range("A10").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _

Cells(IntRow + 1, 1)

End Sub