VBA로 엑셀 데이터 필터기능 적용하기!

 

『 수많은 raw데이터 중에서 내가 원하는 부분만 걸러서 보기 위해서 엑셀의 필터기능을 많이 사용하고 계십니다. 필터기능 역시 VBA를 통해 구현이 가능한데요. 원하는 기준으로 필터 적용 후 필터된 데이터를 복사하여 필요한 부분에 붙여 넣는 코드를 알아보도록 하겠습니다. 이를 통해 업무자동화에 한 발짝 더 다가갈 수 있으면 좋겠습니다. 』

 

 

 

 

VBA로 필터기능 사용하기

VBA 필터기능 사용하기의 기본문법은 다음과 같습니다.

정렬범위.AutoFilter 필터위치, 필터기준

 

기본문법을 살펴보면 정렬범위를 선택 후 필터기준을 통해 필터를 진행하게 됩니다. 기본적으로 VBA는 엑셀에서 일반적으로 사용되는 내용들을 똑같이 구현할 수 있기 때문에 쉽게 배울 수 있다는 장점이 있습니다.

 

그럼 VBA를 사용하여 필터를 진행하는 방법을 이해할 수 있도록 엑셀 데이터를 통해 예를 들어보도록 하겠습니다.

아래의 예시를 살펴보시죠. 

 

<필터 예시>

 

A열은 "축제"가, B열에는 "장소"가, C열에는 "담당자"가 입력된 데이터입니다. 엑셀 필터기능을 통해 "E2"셀의 기준으로 설정한 "서울"을 가지고 데이터에 필터를 적용하였습니다. 기준자체가 "장소"를 나타내고 있기 때문에 "B열"을 기준으로 필터를 적용하게 됩니다. 

 

 

<필터 결과>

 

필터 된 결과를 살펴보면 장소가 "서울"인 곳이 필터 되어 나타나는 것을 볼 수 있습니다.

엑셀에서 사용하였던 필터기능을 VBA를 통해 구현해 보도록 하겠습니다. 코드는 아래와 같습니다.

 

Sub 필터() Dim rng As Range ' 범위 선언 Set rng = Range("a1").CurrentRegion ' "a1"셀부터 "C열"의 데이터가 있는 마지막셀까지의 범위를 rng 변수로 설정 rng.AutoFilter 2, Range("e2") ' rng로 설정된 범위에 필터를 적용하되, 두번째 열인 "B"열에 "E2셀"의 "서울"을 기준으로 필터   End Sub

 

Set rng = Range("a1").CurrentRegion는 "a1"셀부터 "C열"의 데이터가 있는 마지막셀까지의 범위를 rng 변수로 설정하는 코드입니다. 앞서 배웠던 CurrentRegion을 통해 연속된 데이터 전체를 범위로 설정하였습니다.

 

rng.AutoFilter 2, Range("e2")는 rng로 설정된 범위에 필터를 적용하되, 두 번째 열인 "B"열에 "E2셀"의 "서울"을 기준으로 필터를 적용하는 코드입니다. 만약 필터를 하고자 하는 열이 3번째 열이라면 2 대진 3을 적용시켜 주면 됩니다. 다만 열이 변경된다면 필터의 기준도 변경되어야 하기 때문에 "E2셀"의 기준 데이터도 함께 변경되어야 합니다.

 

 

동일한 예제를 가지고 한 가지 기능을 추가시켜 보겠습니다.

<필터 해제>

 

 

위 이미지상에 나와있는 코드를 자세히 살펴보시면 조금 달라진 코드를 발견할 수 있습니다. 해당 워크시트에 필터가 걸려있는지, 걸려있지 않은지를 확인하여 필터가 걸려있는 경우라면 필터를 해제하여 모든 데이터를 보여줄 수 있게 해주는 코드가 추가되었습니다. 

 

 

세부 내용은 코드를 살펴보면서 설명드리도록 하겠습니다.

기존 설명이 완료된 코드에 대한 설명은 생략하겠습니다.

 

Sub 필터() Dim sh As Worksheet, rng As Range ' sh라는 변수를 통해 워크시트 개체를 넣어줍니다. Set sh = Sheets("필터") ' "필터"라는 명칭의 워크시트를 sh 변수에 담아줍니다. Set rng = Range("a1").CurrentRegion If sh.AutoFilterMode = False Then rng.AutoFilter ' 필터모드가 적용되어 있지 않다면 필터모드를 적용해 줍니다. If sh.FilterMode Then sh.ShowAllData ' 필터가 적용되어 있다면 필터를 해제하여 전체 데이터를 보여줍니다. rng.AutoFilter 2, Range("e2") rng.SpecialCells(xlCellTypeVisible).Copy Range("a22") ' rng범위 중 화면에 보이는 데이터들만 복사하여 "a22"셀에 붙여넣습니다. End Sub

 

"필터"로 이름이 부여된 워크시트에 필터여부를 확인하기 위해 Dim sh As Worksheet를 추가하여 변수를 선언해 줍니다.

sh로 선언된 변수에는 Sheets("필터")를 반영하여 "필터" 워크시트에서 작업이 진행되도록 설정하였습니다.

 

If sh.AutoFilterMode = False Then rng.AutoFilter는 "필터" 워크시트 상에 필터모드 적용여부를 확인하는 코드입니다.

만약 필터모드가 적용되어 있지 않다면 필터모드를 적용시켜 주게 됩니다.

 

여기서 필터모드라고 함은 필터의 기준을 정할 수 있도록 하는 ▼모양의 버튼을 의미합니다. 

아래 이미지의 빨간 박스를 참고하시기 바랍니다.

 

<필터모드>

 

If sh.FilterMode Then sh.ShowAllData는 sh개체 내에 필터가 되어 있다면 모든 데이터를 보여주라는 코드입니다.

쉽게 말해 필터를 해제하라는 말과 같은 것입니다.

 

여기까지 코드가 진행되면 어떤 모습일까요?

네. 맞습니다. 처음 예시를 들었던 모습으로 돌아오게 됩니다.

 

<코드 진행 상태>

 

그 후에는 rng.AutoFilter 2, Range("e2")를 사용하여 장소가 "서울"인 데이터를 필터 하게 되며, rng.SpecialCells(xlCellTypeVisible).Copy Range("a22")를 이용하여  "a22"셀에 필터 된 데이터만 복사하여 붙여 넣는 코드가 완성되었습니다.

 

 

필터 조건이 없을때 메시지박스로 안내 후 프로시져 종료하기

 

데이터 관리를 하다보면 조건을 만족하는 데이터가 없는 경우가 있을 수 있습니다.

기본틀은 그대로 유지한 상태에서 조건이 없는 경우에 메시지박스를 통해 안내 후 프로시져를 종료하는 코드를 만들어 보겠습니다. 기존 사용된 코드에서 추가로 사용된 코드만 빨간색 글자로 처리 후 설명드리도록 하겠습니다.

Sub 필터() Dim sh As Worksheet, rng As Range Set sh = Sheets("필터") Set rng = Range("a1").CurrentRegion If sh.AutoFilterMode = False Then rng.AutoFilter If sh.FilterMode Then sh.ShowAllData rng.AutoFilter 2, Range("e2") Range("a22").CurrentRegion.Clear ' "a22"셀부터 연속된 범위에 있는 데이터를 삭제합니다. If rng.SpecialCells(xlCellTypeVisible).Count = 3 Then ' 만약 화편에 보이는 셀의 갯수가 3개일 경우에는      MsgBox "조건의 데이터가 없음"        ' 메시지박스를 통해 데이터가 없음을 알려줍니다.     If sh.FilterMode Then sh.ShowAllData     Exit Sub        ' 메시지박스과 필터모드가 해제되면 프로시져가 종료됩니다. End If rng.SpecialCells(xlCellTypeVisible).Copy Range("a22") End Sub

 

필터 조건에 리스트에 없는 "지방"이라는 값을 아래와 같이 넣은 후 코드를 돌려 보겠습니다.

 

<예시>

 

조건에 맞는 데이터가 없기 때문에 결과는 다음과 같이 나오는 것을 확인할 수 있습니다.

 

<최종 결과>

 

그럼 사용된 코드를 하나씩 살펴보겠습니다. 위에서도 말씀드렸다시피 새롭게 추가된 코드만 붉은색으로 표시하였으며, 해당 코드만 설명드리도록 하겠습니다.

 

Range("a22").CurrentRegion.Clear는 기존에 필터 된 데이터의 값이 복사되어 값으로 붙여진 범위에 값을 삭제하는 코드입니다. 해당 코드는 앞쪽 포스팅에서 다루었으니, 지나가도록 하겠습니다.

 

<count = 3>

 

If rng.SpecialCells(xlCellTypeVisible).Count = 3 Then는 필터 후 rng범위 내에서 화면에 보이는 셀의 수가 3개 일 때를 의미합니다. 1행에 있는 분류제목만 카운팅 하면 3개라는 것을 알 수 있는데요. 즉, 조건에 맞는 데이터가 하나도 나오지 않는 경우를 찾는 코드입니다. 

 

<메시지박스 출력>

 

조건에 맞는 데이터가 없다면 MsgBox "조건의 데이터가 없음"를 통해 메시지박스로 해당 내용을 알려준 후 Exit Sub를 통해 프로시저를 종료하게 됩니다.

 

중간에 If sh.FilterMode Then sh.ShowAllData를 넣어준 이유는 조건에 맞는 데이터가 없을때 필터를 해제한 후 프로시저를 종료하기 위함이니, 불필요하다고 느끼실 경우에는 삭제 후 코드를 작성하여도 무방합니다. 프로시저가 완료되면 처음 시작하였던 형태의 데이터의 모습으로 돌아가며 종료됩니다.

 

<처음 모습으로 되돌아온 모습>

 

---

마치며,

지금까지 VBA를 사용하여 로우데이터에 필터를 적용하는 방법에 대해서 알아보았습니다. 간단한 작업의 경우는 엑셀상에서 직접 필터를 적용하는 것이 훨씬 빠를 수 있습니다만, 루틴업무에 적용시키기 위해서는 VBA를 사용하는 것이 훨씬 효율적이기 때문에 시간과 정성을 투자하는 것입니다.

 

이러한 루틴업무를 단축시키는 작업을 하나씩 진행해 나간다면 업무스킬의 향상은 물론 더 많은 업무를 빠르고 정확하게 처리할 수 있는 능력이 생기게 될 것입니다. 엑셀은 기술이 아닌 기능입니다. 반복연습만 하여도 충분히 효율적인 사용이 가능하다는 점을 기억하시고 반복연습을 진행해 보시기 바랍니다.

 

 

▼▼▼함께 읽으면 좋은 글▼▼▼

VBA 반복문 1탄(for~next 사용하기)

VBA 반복문 2탄(For each ~ Next 사용하기)

 

VBA 빈셀 채우기!(피벗테이블 오류 최소화 방법)

 

VBA로 셀병합 해제 후 빈셀 채우기(빈셀 채우기와 찰떡궁합!)

 

VBA 워크시트함수(Match, Min함수), Split함수 사용법 익히기

 

 

고생 끝에 낙이 온다.