Excel [지식인 해결 시리즈] 002. 공백 셀 제거 후 정렬

 

전체 목차

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)

 

포스트 목차.

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

01. 문제

 

문제를 정리하면 B2:H6으로 보이는 구간에 있는 데이터 중에서 공백은 제거한 후에, 데이터를 정렬해서 L열에 채워달라는 이야기 같습니다.

 

02. 문제해결 방법

 

우리가 해야 될 일을 정리하면 다음과 같습니다. 

  (1) B2:H6까지의 영역을 순회하면서 공백이 아닌 데이터를 수집하여 L열 2행부터 채워 넣는다.

  (2) L열에 있는 데이터를 정렬한다.

 

- 데이터 순회 방법

VBA에서 특정 영역을 순회하는 방법은 이전 포스트에서 다룬 적이 있다.

https://eggdrop.tistory.com/11

Excel 매크로 (VBA) - 006. 셀 병합 하기

위 포스트에서 문제해결을 위한 도구 부분을 참고하면 For Each ... In ...Next 구문에 대한 설명이 있다.

 

B2:H6까지의 영역을 순회하면서 공백이 아닌 값이 나올경우 L2부분부터 채워나가면된다.

 

이후에 특정 영역을 정렬하는 방법을 알아야 한다.

 

- 데이터 정렬

데이터 정렬은 Range 객체의  Sort 메소드를 이용하여 진행할 수 있다. 

Range.Sort 형태로 사용할 수 있으며, 여러 선택형 파라미터들을 가지고 있는데, Sort 에 대해서는 추후에 다시 자세히 다루도록 하겠다.

 

여기서는 다음과 같은 사용법만 익히고 넘어가자

Range 객체 후에 .Sort 형식으로 사용할 수 있으며, 정렬할 기준 행을 key1로 정할 수 있으며, Order1 파라미터를 통해서 오름차순(xlAscending)/내림차순(xlDescending)을 정할 수 있다. 

Range("L2:L4").Sort key1:=Cells(1,1), Order1:=xlAscending 

 

03. 문제 해결 

 

다음과 같은 Sub 프로시저를 작성해보자. 

Sub clearWhiteSpaceAndSort()
    'range 객체 선언
    Dim rng As Range
    'range 객체에 B2:H6 할당
    Set rng = ActiveSheet.Range("B2:H6")
    '공백이 아닌 결과물이 저장될 행 위치를 지시하는 변수 선언    
    Dim writeRowPoint As Long
    'L2가 데이터 채우기 시작점이므로, 2로 초기화
    writeRowPoint = 2
    
    '위에서 정의한 range 객체의 모든 셀을 순회하며 데이터 비교
    For Each Item In rng.Cells
        '공백이 아닌 경우
        If Item.Value <> "" Then
            'L2부터 순서대로 데이터 채우기
            ActiveSheet.Cells(writeRowPoint, 12).Value = Item.Value
            '행 위치 1증가
            writeRowPoint = writeRowPoint + 1
        End If
    Next Item
    '공백이 아닌 데이터가 없는 경우 writeRowPpoint가 2이기 때문에 그런 경우는 정렬 불필요
    If writeRowPoint > 2 Then
        'L열에 데이터가 있는 부분을 L열 기준으로 오름차순 정렬, For Each 문을 통과하면서 wireRowPoint값을 1증가시켜놨으므로 다시 1을 빼준 범위까지만 정렬한다.
        ActiveSheet.Range(ActiveSheet.Cells(2, 12), ActiveSheet.Cells(writeRowPoint - 1, 12)).Sort key1:=ActiveSheet.Range("L2"), Order1:=xlAscending
    End If
 
End Sub

 

Sub 프로시저 실행 전에, 데이터 영역에 다음과 같은 데이터를 채워둔다.

 

 

Sub 프로시저를 실행하면 L열에 데이터가 정렬되어 들어가는 것을 확인할 수 있다.