[엑셀/VBA] 엑셀 불필요한 데이터(행) 삭제 및 중복데이터 삭제 및 정렬

지난 번 엑셀 VBA를 통하여 파일통합을 진행하였습니다. 

 

2022.11.22 - [엑셀(Excel)] - [엑셀/VBA] 폴더 내 엑셀파일 한번에 통합하기(엑셀파일병합, 자동화기본)

2022.10.24 - [엑셀(Excel)] - [엑셀] 시트별 데이터 통합하기(엑셀VBA활용)

 

이제 폴더 내 파일 및 시트별로 분산된 데이터의 경우 통합하였으니, 불필요한 데이터를 삭제하는 방법을 알아보도록 하겠습니다.

 

통합된 데이터를 깔끔하게 정제하기 위한 프로세스는 다음과 같을 것 같습니다.

 ① VBA를 통한 파일 통합(시트OR폴더 내 엑셀파일)  

 ② 불필요한 행 삭제(자동화를 통한 통합 시 불필요한 데이터 들어갈 수 있으므로)

 ③ 중복된 데이터 삭제(자동화를 통한 통합 시 불필요한 데이터 들어갈 수 있으므로)

 ④ 원하는 형태로 정렬

 

이 중에서 가장 자동화가 필요한 부분은 ① 입니다. 가장 손이 많이가고 시간을 많이 잡아먹습니다. 

②, ③, ④의 경우 수작업으로 해도 파일통합만 된다면 그다지 어렵지 않고 엑셀파일 기본적 기능으로도 쉽게 구현이 가능합니다. 하지만 완전 자동화를 위해 배워보겠습니다. 

 

먼저 오늘의 결론입니다. 

'삭제됨'이라고 되어 있는 행을 삭제해줄 예정

 

#01. 행삭제 코드 안내

 

'이름' 열에 '삭제됨'이라고 되어 있는 행을 삭제해줄 예정입니다.

 

먼저 행삭제 기본으로 코드를 올려보도록 하겠습니다. (수정 전 행삭제 코드) 

Sub NotUseRow_Delete()
'변수 정의
Dim i As Long
	'반복문 For ~ Next
    For i = 4 To 15
        If Cells(i, "e") = "삭제됨" Then '삭제됨이라고 적힌 행은 
            Cells(i, "e").Resize(, 3).Delete '삭제해줘
        End If
    Next
End Sub

 

For ~ Next 반복문을 통해 셀을 잘 삭제해주는 코드처럼 보입니다. (↑ 위 코드는 잘못된 코드입니다.)

 

하지만 !! 

결과적으로는 원하는대로 삭제되지 않았습니다. 

연속된 행에 대해서 삭제하지 못함

 

위에서 순차적으로 '삭제됨'행을 삭제해나가고 있었으나 14행의 '삭제됨'행은 삭제되지 않았습니다.

 

그 이유는 엑셀에서 셀 삭제 시 기존 행을 위로 올라가는 형태입니다.

저희가 엑셀에 명령 내릴 땐 13행 삭제해줘 → 13행 삭제 → 14행 삭제해줘 → 14행 삭제 → 15행 삭제해줘 → 15행 삭제 라고 명령 내렸지만,

실제로 엑셀이 명령을 받게되면 13행 삭제해줘 → 13행 삭제 → 14행 삭제해줘 하는 순간 이미 14행이 다시 13행으로 올라가 버리게 됩니다. 

그래서 중간에 있는 14행만 삭제되지 않은 채로 나타나게 됩니다. 연속된 행에서 원하는 형태로 삭제하지 못함을 볼 수 있습니다. 

 

여기서 중요한 부분이 바로 행삭제 VBA코드를 쓸 때는 위에서 아래로 삭제하는 게 아닌 아래에서 위로 삭제처리를 해야한다는 것입니다. 

 

[수정된 행삭제 기본코드]

Sub NotUseRow_Delete수정()

Dim i As Long

    For i = 15 To 4 Step -1 '15행부터 4행까지 아래에서 위로 반복
        If Cells(i, "e") = "삭제됨" Then
            Cells(i, "e").Resize(, 3).Delete
        End If
    Next
    
End Sub

 - 수정된 코드는 '행 삭제'의 경우 반드시 !! 아래에서 위로 삭제방향을 지정해야 합니다.

 - 그래야 연속된 데이터에서도 누락없이 삭제가 가능합니다. 

 

[Union메서드 활용한 행삭제 코드]

Sub 행삭제_union()
'union 비연속적으로 떨어진 셀 합쳐주는 역할

Dim r As Range
Dim i As Long, lR As Long

lR = Cells(Rows.Count, "e").End(xlUp).Row

For i = 4 To lR
    If Cells(i, "e") = "삭제됨" Then
        If r Is Nothing Then 'r에 아무것도 없는 경우
            Set r = Cells(i, "e").Resize(, 3)
        Else
            Set r = Union(r, Cells(i, "e").Resize(, 3))
        End If
    End If
Next

If Not r Is Nothing Then r.Delete 'r값이 존재하면 삭제

End Sub

 

  - 이번 코드는 Union 메소드를 알아야 사용할 수 있습니다.

If Cells(i, "e") = "삭제됨" Then
    If r Is Nothing Then 'r에 아무것도 없는 경우
        Set r = Cells(i, "e").Resize(, 3)
    Else
        Set r = Union(r, Cells(i, "e").Resize(, 3))
    End If
End If

특히 이 안쪽 부분의 Union메소드를 통한 코드는 외워주시면 됩니다. 

'삭제됨'이라고 되어 있는 부분만 r이라는 변수에 저장합니다. 

 

그 이후 If Not r Is Nothing Then ~이라는 구문을 통해 r값이 존재하면 ~~ r.delete : r을 삭제해줘 라는 뜻입니다. 

다음은 중복데이터 삭제를 확인해보겠습니다. 

#02. 중복된 데이터 삭제

중복된 데이터 삭제

 - 원본 데이터를 보니 2022.02 데이터 中 3명의 동일한 데이터가 중복 취합되었습니다.

 - 아래데이터를 삭제해주도록 합니다. 

Sub 중복제거()

Dim rngD As Range

Set rngD = Range("e4", Cells(Rows.Count, "g").End(xlUp))
    rngD.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlNo 'Array(1,2,3) 열 3개, Header 머릿글 포함여부
    
End Sub

- 결국 범위를 지정해준뒤 RemoveDuplicates 를 통해 Columns:=Array(열 수 만큼 ,로 지정) 해주면 됩니다. 

 

 

좀 더 복잡한 코드는 다음과 같습니다. 

Sub 중복제거2()

Dim rng As Range, rngX As Range
Dim lR As Long, lC As Long, i As Long, j As Long

Set rng = Range("e4", Cells(Rows.Count, "g").End(xlUp))

lR = Cells(Rows.Count, "e").End(xlUp).Row
lC = rng.Columns.Count
rng(1, lC + 1).Resize(rng.Rows.Count, 1) = "=e4&f4&g4"

For i = 4 To lR - 1
    For j = i + 1 To lR
        If Cells(i, "h") = Cells(j, "h") Then
            If rngX Is Nothing Then
                Set rngX = Cells(j, "e").Resize(1, lC + 1)
            Else
                Set rngX = Union(rngX, Cells(j, "e").Resize(1, lC + 1))
            End If
        End If
    Next
Next
        
If rngX Is Nothing = 0 Then rngX.Delete
Columns("h") = ""

End Sub

 - h4열에 e4&f4&g4로 연결된 데이터를 전체 데이터 셀 하나하나와 비교해서 중복되면 삭제하는 형태입니다. 

여기도 위에 소개했던 Union 메소드를 사용하였습니다.

 

#03. 데이터 오름차순/내림차순 정렬

연월 데이터 정렬

Sub 정렬()

Dim rng As Range
Set rng = Range("e4", Cells(Rows.Count, "g").End(xlUp))

Sheet1.Sort.SortFields.Clear '사용자지정목록 기준으로 정렬했던 흔적 지우기

rng.Sort key1:=rng(1, 2), order1:=xlAscending, Header:=xlNo  'rng의 1행 2열 오름차순 정려

End Sub

 - rng.Sort 명령어를 통해 오름차순 정렬이 가능합니다.

 - 내림차순 명령어는 xlDescending 입니다.

 - Header는 xlNo의 경우, 머릿글 제외 xlYes 머릿글포함 입니다.

 - 현재, 머릿글 포함하지 않는 범위를 Set으로 잡아두었기 때문에 머릿글 포함이 아직 없습니다.

 

 

이제 엑셀 VBA를 통해서 데이터 취합 자동화를 완성하였습니다.

엑셀 파일을 통합하고, 필요 없는 데이터를 삭제하고, 혹시나 잘못 취합하여 중복된 데이터는 삭제하고, 마지막으로 우리가 보기 좋게 정렬하는 방법까지 배웠습니다. 

 

이제 클릭 한 번으로 엑셀 취합 업무를 자동화하시기 바랍니다.