엑셀 VBA 매크로 이용해서 특정 조건의 데이터를 삭제하고 싶을 때

엑셀시트의 자료에서 특정행을 삭제하는데 수작업으로 가능하지만 양이 많을때는 VBA 코드를 짜서 편리하게 이용할수 있습니다. 아래 예제는 특정조건에 맞는 데이터가 셀에 포함되어 있을 때 행 전체를 삭제하는 샘플입니다.

 

 

▼ 샘플데이터에서 소속이 “관리부” 인 데이터를 삭제하고자 합니다. 데이터가 많은 경우 일일이 삭제하는 것이 비효율적이고 쉽지 않기 때문에 매크로를 만들어 작업 속도를 올려보세요.

 

▼ 데이터 자동 삭제 매크로를 만들어 보겠습니다. 개발 도구 > 매크로 리본 메뉴를 클릭합니다. 

 

▼ 매크로를 실행할 함수를 만들어야 합니다. 매크로 이름에 함수명을 넣고 오른쪽 사이드 메뉴에서 만들기 버튼을 클릭합니다. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 다른 셀에 일치하는 코드 값에 데이터 찾아 추가하기 ▶ 엑셀 VBA 마지막 행과 열의 수 찾기 ▶ 엑셀 VBA 시트에서 데이터 셀 영역 구하기 ▶ 엑셀 VBA – 매크로 이용해서 특정 조건의 데이터를 삭제하고 싶을 때 ▶ 엑셀 VBA 입력된 값 형식 확인 Information 관련 함수 모음

 

 

 

 

 

 

 

▼ 함수에 들어가는 전체 소스는 다음과 같습니다.

Sub 관리부_삭제()
   
    Dim co As Long, i As Long
    Dim join As String
    Dim buNum As String
    Dim deleteRow As Integer
 
    '추가할 시트에 Row count 를 알아옵니다
    co = ActiveSheet.Range("B3").CurrentRegion.Rows.Count
 
    Cells(3, "B").Select
 
    For i = 0 To co
        join = Selection.Offset(0, 0) ' 소속셀값
        buNum = Selection.Offset(0, 1) ' 부서번호셀값
        If join = "관리부" Then
            Selection.EntireRow.Delete
            co = co - 1
            deleteRow = deleteRow + 1
        Else
            Selection.Offset(1, 0).Select
        End If
    Next
   
End Sub

 

 

▼ 핵심 소스만 간략하게 설명하겠습니다. 특정 조건에 맞는 리스트를 삭제하기 위해 현재 추가된 전체 행 개수를 알아야 합니다. CurrentRegion 는 좌측 최상단에서 연속된 셀의 데이터 전체를 가져옵니다. CurrentRegion 를 이용해 행의 개수를 알아옵니다. 속성값은 Row count 입니다. 이 값으로 For 문을 돌립니다.

co = ActiveSheet.Range("B3").CurrentRegion.Rows.Count

 

다음은 검색할 데이터 시작 지점을 지정해야 합니다.

Cells(3, "B").Select

 

위에서 선택된 영역을 가져와 Offset 을 이용해 소속 셀의 값을 알아 옵니다.

join = Selection.Offset(0, 0) ' 소속셀값

 

삭제할 셀의 비교 값으로 동일한지 여부를 판단하고 Selection.EntireRow.Delete 로 행 전체를 삭제합니다.

If join = "관리부" Then
     Selection.EntireRow.Delete
     co = co - 1

 

▼ 함수의 소스 작성이 끝났다면 매크로를 돌려 데이터를 삭제해 보겠습니다. 매크로 창에서 실행할 이름을 선택하고 오른쪽에 실행 버튼을 클릭합니다. 

 

▼ 매크로 실행 결과는 다음과 같습니다. “관리부” 데이터가 모두 삭제되었습니다. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 다른 셀에 일치하는 코드 값에 데이터 찾아 추가하기 ▶ 엑셀 VBA 마지막 행과 열의 수 찾기 ▶ 엑셀 VBA 시트에서 데이터 셀 영역 구하기 ▶ 엑셀 VBA – 매크로 이용해서 특정 조건의 데이터를 삭제하고 싶을 때 ▶ 엑셀 VBA 입력된 값 형식 확인 Information 관련 함수 모음

 

 

 

◎ 질문에 대한 답변

 

 

1. 질문 : 저의 경우 c열에 (기업), (우리), (국민) 등이 있는 경우 삭제하는 매크로작성이 목적인데 예제를 보고 응용하다가 막혀버렸는데 코멘트 부탁드립니다.

 

답변 : 문자열에서 비교할 때 특정 단어나 문자의 포함 여부를 확인하고 싶다면 instr 함수를 사용합니다. 두 번째 인수로 입력한 문자가 포함되어 있다면 0 이상을 반환하기 때문입니다. 질문하신 소스를 다음과 같이 수정했습니다. 

 

Sub ASASDASDDA()
    
    Dim r As Range
    Dim i As Long, lR As Long
    
    lR = Cells(Rows.Count, "C").End(xlUp).Row
    
    On Error Resume Next
    
    For i = lR To 2 Step -1
        Debug.Print Cells(i, "C")
        Debug.Print InStr(Cells(i, "C"), "국민")
        
        If InStr(Cells(i, "C"), "국민") > 0 Or InStr(Cells(i, "C"), "하나") Then
            If r Is Nothing Then
                Set r = Cells(i, "C").Resize(, 2)
            Else
                Set r = Union(r, Cells(i, "C").Resize(, 2))
            End If
        End If
    Next
    
    On Error GoTo 0
    r.EntireRow.Delete
 
End Sub

 

 

2. 질문 : 혹시 '관리부'만 남기고 나머지는 다 삭제하고 싶다면 어떻게 해야 할지 여쭤봐도 될까요? 매크로를 실행할 때 이 '관리부'에 해당하는 텍스트를 텍스트박스에 입력받아서 실행하게 하고 싶은데 그렇게 하지 않고 바로 소스에서 바꿔 실행하는것만 되어도 정말 엄청 감사하겠습니다 ㅠㅠ!!

 

답변 : C 라고 입력한 열 주소를 B 로 변경합니다. 그리고 전산부 외에 다른 데이터 모두 삭제하고 싶다면 IF ~ OR 을 이용해서 삭제할 여러 항목들을 조건으로 입력합니다. 남기고 싶은 부서만 제외하면 되겠죠.

 

If Cells(i, "C") = "관리부" Or Cells(i, "C") = “영업부” Then

 

 

3. 질문

저걸 하나의 목록? 리스트? 배열? 등으로 만들고 일괄적으로 불러들이면 코드가 깔끔할꺼 같은데 뭐라고 검색해야 할까요?

 

답변

 

Sub delete_row()

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

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

    On Error Resume Next
    For i = lR To 2 Step -1
        If InStr(1, Cells(i, "C"), "(제일)") Or InStr(1, Cells(i, "C"), "(하나)") Or InStr(1, Cells(i, "C"), "(신한)") Then
            If r Is Nothing Then
                Set r = Cells(i, "C").Resize(, 2)
            Else
                Set r = Union(r, Cells(i, "C").Resize(, 2))
            End If
        End If
    Next
    On Error GoTo 0
    r.EntireRow.Delete

End Sub

 

 

▼  위의 소스를 아래와 같이 고칩니다. 

Sub delete_row()

    Dim r As Range
    Dim rTitle As Range
    Dim i As Long, lR As Long
    lR = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
    
    On Error Resume Next
    
    Set rTitle = Sheets("Sheet2").Range("B3:B6")
    For i = lR To 2 Step -1
        Debug.Print Sheets("Sheet1").Cells(i, "C")
        For Each tt In rTitle
            Debug.Print tt
            If InStr(1, Sheets("Sheet1").Cells(i, "C"), tt) Then
                If r Is Nothing Then
                    Set r = Sheets("Sheet1").Cells(i, "C").Resize(, 2)
                Else
                    Set r = Union(r, Sheets("Sheet1").Cells(i, "C").Resize(, 2))
                End If
                
                Exit For
            End If
        Next tt
       
    Next
    On Error GoTo 0
    
    r.EntireRow.Delete

End Sub

  

▼ 소스를 설명하겠습니다. 핵심은 다른 시트에 입력한 제외 목록 영역을 읽어와서 For 문으로 반복하면서 비교하는 것입니다. Sheet2 에 제외할 은행 목록을 입력하고 영역에 데이터를 가져옵니다.

 

Set rTitle = Sheets("Sheet2").Range("B3:B6")

 

  

▼  다음은 for 문을 돌면서 삭제할 은행인지 IF 로 비교합니다. 만약 동일한 은행이라면 삭제하고 Exit For 로 해당 반복문을 빠져나옵니다.

For Each tt In rTitle
	If InStr(1, Sheets("Sheet1").Cells(i, "C"), tt) Then
    	Exit For 
	End If
Next tt