Excel VBA로 특정 값과 다른 값만 자동 필터링하는 방법 (전체 시트·특정 범위)

Excel VBA로 특정 값이 아닌 값만 자동 필터링하기

이 글에서는 Excel VBA를 활용해 특정 값과 같지 않은 값만 골라내는 오토피터(AutoFilter) 방법을 소개합니다. 워크시트 전체에서 원하는 열만 추출하는 방법과, 지정한 셀 범위 안에서만 필터링하는 방법을 모두 배우실 수 있습니다.

먼저 완성된 코드의 전체 모습부터 간단히 살펴보겠습니다.

전체 코드 미리 보기:

Sub Autofilter_Values_from_Whole_Worksheet()

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

특정 값과 같지 않은 값을 VBA로 자동 필터링하는 2가지 방법

그럼 지금부터 본론으로 들어가겠습니다. 먼저 워크시트 전체를 대상으로 필터링하는 방법을 알아본 뒤, 이어서 특정 범위만 대상으로 하는 방법을 다루겠습니다.

방법 1. 워크시트 전체에서 특정 값과 다른 값만 필터링하기

먼저 Excel VBA로 워크시트 전체에서 특정 값과 같지 않은 값을 찾아 다른 시트로 옮기는 방법을 배워보겠습니다.

여기 Sheet1이라는 워크시트에는 학생들의 이름, 시험 점수, 그리고 등급 데이터가 들어 있으며, 데이터는 A1 셀부터 시작됩니다.

목표는 등급이 F가 아닌 학생들만 골라내어 Sheet2에 자동으로 정리하는 것입니다.

⧪ 1단계: 입력값 설정하기

코드에 필요한 입력값부터 지정합니다. 여기에는 원본 워크시트 이름(Sheet1), 대상 워크시트 이름(Sheet2), 필터링할 열 번호(1, 3), 기준 값이 있는 열(3열), 그리고 기준 값(F)이 포함됩니다.

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

⧪ 2단계: For 루프로 값 필터링하기

다음으로 For 루프를 돌면서 조건에 맞는 행들을 원본 워크시트에서 대상 워크시트로 하나씩 옮깁니다.

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

완성된 VBA 코드 전체는 다음과 같습니다.

⧭ VBA 코드:

Sub Autofilter_Values_from_Whole_Worksheet()

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

⧭ 실행 결과:

입력값을 상황에 맞게 수정한 뒤 코드를 실행하세요. 단, 대상 워크시트(Sheet2)는 반드시 미리 만들어 두어야 합니다. 대상 시트가 없으면 오류가 발생합니다.

코드를 실행하면 지정한 열(이 예제에서는 1열과 3열)의 데이터 중 기준 값(F)과 일치하지 않는 행만 대상 워크시트에 자동으로 정리됩니다.

더 읽어보기: VBA로 동일한 필드에 여러 조건을 적용해 AutoFilter 사용하기 (4가지 방법)

방법 2. 특정 셀 범위에서 특정 값과 다른 값만 필터링하기

앞서 워크시트 전체를 대상으로 필터링하는 방법을 배웠습니다. 이번에는 특정 범위에 한정해서 값을 추출하는 방법을 알아보겠습니다.

이번에는 Sheet3이라는 워크시트에 학생들의 이름, 점수, 등급 데이터가 들어 있으며, 데이터 범위는 B3 셀부터 D15 셀까지입니다.

이번 목표는 같은 워크시트 안에서 등급이 F가 아닌 학생들만 골라내어 F3 셀부터 정리하는 것입니다.

⧪ 1단계: 입력값 설정하기

먼저 코드에 필요한 입력값을 지정합니다. 이번에는 원본 워크시트(Sheet3), 원본 범위(B3:D15), 대상 워크시트(Sheet3), 대상 셀(F3), 필터링할 열(1, 3), 기준 값이 있는 열(3열), 기준 값(F)을 차례로 입력합니다.

Source_Worksheet = "Sheet3"
Source_Range = "B3:D15"

Destination_Worksheet = "Sheet3"
Destination_Cell = "F3"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

⧪ 2단계: For 루프로 값 필터링하기

마찬가지로 For 루프를 사용해 조건에 맞는 값들을 대상 셀부터 순서대로 복사합니다.

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).Range(Source_Range).Rows.Count
    If Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Range(Destination_Cell).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

완성된 VBA 코드 전체는 다음과 같습니다.

⧭ VBA 코드:

Sub Autofilter_Values_from_Specific_Range()

Source_Worksheet = "Sheet3"
Source_Range = "B3:D15"

Destination_Worksheet = "Sheet3"
Destination_Cell = "F3"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).Range(Source_Range).Rows.Count
    If Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Range(Destination_Cell).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

⧭ 실행 결과:

입력값을 수정한 뒤 코드를 실행하세요. 대상 워크시트가 다른 시트라면 반드시 미리 만들어 두어야 오류 없이 실행됩니다.

실행하면 지정한 열(이 예제에서는 1열과 3열)의 데이터 중 기준 값(F)과 다른 값만 대상 워크시트의 대상 셀(이 예제에서는 F3)부터 정리되어 나타납니다.

더 읽어보기: [해결 방법] Range 클래스의 AutoFilter 메서드 실패 오류 고치기 (5가지 해결책)

주의할 점

• 새 워크시트로 필터링 결과를 출력하기 전에 반드시 해당 워크시트를 먼저 만들어야 합니다. 그렇지 않으면 실행 오류가 발생합니다.
• 워크시트 전체 데이터를 다룰 때는 VBA의 UsedRange 속성을 활용했습니다. UsedRange 속성의 동작 방식이 궁금하다면 관련 문서를 참고해 보세요.

마무리

지금까지 Excel VBA를 사용해 특정 값과 같지 않은 값만 자동으로 필터링하는 두 가지 방법을 알아보았습니다. 궁금한 점이 있다면 언제든지 질문해 주세요. 더 많은 Excel 팁과 유용한 글은 ExcelDemy에서 확인하실 수 있습니다.

함께 보면 좋은 글

• AutoFilter가 켜져 있는지 확인하는 Excel VBA 방법 (4가지 쉬운 방법)
• VBA AutoFilter: 작은 값부터 큰 값 순으로 정렬하기 (3가지 방법)
• Excel VBA로 AutoFilter 후 보이는 행만 복사하는 방법
• Excel VBA: AutoFilter가 설정되어 있으면 제거하기 (7가지 방법)