[엑셀VBA] 특정 조건 만족하는 데이터 Set 불러오기(Feat. vba 맛보기)_2편

엑셀 vba로 특정 데이터만 추출하기 1편에 이어 2편 시작하도록 하겠습니다.

 

지난 번에는 '속성'이 '풀'인 데이터만 추출하는 코드를 보여드렸고, With문 까지 설명드렸습니다.

 

전체 데이터 중 특정 속성 만족하는 값 불러오기

■ 전체코드

Sub ExtractData()

    Dim i As Long, lR As Long, lC As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
    With Sheet1

'---------------▼ 아래부터 설명시작 ▼ ------------------------------
        If .Range("N2") = vbNullString Then 'vbNullString = ""
            MsgBox "먼저 N2셀을 선택하세요"
            Exit Sub
            
        End If
        
        If Sheet2.Range("A1").CurrentRegion.Cells.Count > 11 Then
            Set rng = Sheet2.Range("A1").CurrentRegion
            Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
            rng.Clear
            
        End If
        
        lR = .Cells(Rows.Count, "a").End(xlUp).Row 'a열 마지막 셀의 행의 번호 보여줘
        lC = .Cells(1, Columns.Count).End(xlToLeft).Column '1행 마지막 셀의 열의 번호 보여줘
        
        For i = 2 To lR
            If .Cells(i, "C") = .Range("N2") Then
                .Cells(i, "a").Resize(1, 11).Copy Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1) 'Copy & Paste
            End If
        Next
    
    End With

    Application.ScreenUpdating = True

End Sub

1. IF Then ~ End if

먼저 IF문 입니다. 

If .Range("N2") = vbNullString Then 'vbNullString = ""
    MsgBox "먼저 N2셀을 선택하세요"
    Exit Sub
            
End If

IF문은 엑셀 함수와 거의 유사합니다.

 

[엑셀 함수 IF ]

=IF(조건, 조건이 참일 경우 값, 조건이 거짓일 경우 값) 

 

[엑셀 VBA IF ]

If 조건이 참일 경우 Then

    값

End If

 

함수와 다른 점은 Then과 End if를 써줘야 한다는 점입니다.

 

If .Range("N2") = vbNullString Then

.Range("N2") 는 Sheet1의 N2셀이며, 그 값이 vbNullstring 은 '빈 값'일 경우를 의미합니다. 

 

MsgBox "먼저 N2셀을 선택하세요"

즉, N2셀이 공백일 경우 MsgBox (메세지박스로) "먼저 N2셀을 선택하세요"라고 알려줘 라는 뜻입니다.

 

Exit Sub

Exit Sub : 그리고 공백일 경우, 현재 매크로를 종료합니다. 

 

If Sheet2.Range("A1").CurrentRegion.Cells.Count > 11 Then
    Set rng = Sheet2.Range("A1").CurrentRegion
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    rng.Clear
End If

 

다음 문장입니다. 

CurrentRegion의 경우, 엑셀 단축키 "Ctrl + A" 와 동일합니다. 

Ctrl + A를 누를 경우, 데이터가 있는 연속된 셀이 모두 선택됩니다. 

CurrentRegion.Cells.Count > 11 인지의 뜻은, 데이터가 있는 연속으로 선택된 셀의 갯수가 11개가 넘는 지 확인하는 것입니다.

왜 11개인지는 헤더 때문에 그렇습니다.

 

저희는 헤더는 기본적으로 적어둔 상태에서 아래 내용만 복사 붙여넣기로 바뀌는 매크로를 짜고 있기 때문에 11보다 큰지 작은지에 대해 조건식을 구성하였습니다. 

a ~ k열의 갯수는 11개입니다. 

 

rng를 Set으로 잡아주고 난 후에 다시 범위를 재설정 합니다. 

Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

이런 부분이 확실히 VBA가 어려워서 함수로 엑셀하는 것 같습니다.

VBA Offset(행,열)로 이동합니다. 

기존 데이터가 A1셀에 있다고 가정하는 경우, 

offset(0,0)은 이동이 없으니 그대로 A1

offset(1,0)은 아래로 행 1칸, 열 그대로 이므로 A2

offset(0,1)은 행 그대로, 열 오른쪽으로 한칸 이므로 B1

offset(1,1)은 행 아래로 1칸, 열 오른쪽으로 한칸 이므로 B2

VBA offset(행, 열) 이동

 

rng.offset(1)이라는 말은 rng.offset(1,0)과 동일합니다. 

다시 말해서 rng범위를 아래로 한칸 이동시켰다는 말입니다. 

rng범위를 예를 들어 "A1:C11"까지라고 할 경우, offset(1)은 "A2:C11"로 변경된다는 뜻입니다. 

위에 한칸만 아래로 내려오며 전체이동은 아닙니다.

 

.Resize(rng.Rows.Count - 1)

는 Resize는 범위 재설정을 해준다는 말인데,, 어떻게 해줄꺼냐? 라고 물었던 부분이고

rng.Rows.Count가 이제 rng라는 범위에서 행의 숫자를 세준다는 말입니다.

위의 예시처럼 "A2:C11"를 rng라는 변수로 잡았을 경우 

행과 열이 각 몇개일까요? 

행은 2행 ~ 11행이니 10개(11-2+1) 입니다.

열은 A,B,C 이니 총 3개 입니다.

 

그럼 "A2:C11"은 행, 열로 표기할 경우 (11,3) 범위로 인식이 되겠네요

 

rng.Rows.Count의 경우 11이며, -1이니 rng.Rows.Count -1 은 10 입니다. 

 

위의 매크로에서 -1을 해준 이유는 헤더 부분은 삭제하면 안되기 때문에 그렇습니다. 

 

        If Sheet2.Range("A1").CurrentRegion.Cells.Count > 11 Then

            Set rng = Sheet2.Range("A1").CurrentRegion

            Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

            rng.Clear

 

이제 그럼 이 명령어가 이해가 되시려나요?

 

사람 말로 풀어서 설명하면

 

If Sheet2.Range("A1").CurrentRegion.Cells.Count > 11 Then

만약에 Sheet2의 범위를 지정하는데 11보다 크면(=헤드 데이터 외에 아래 데이터가 있으면)

 

Set rng = Sheet2.Range("A1").CurrentRegion

Sheet2의 A1셀 중 연속적으로 데이터가 있는 셀의 범위를 지정하는데

 

Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

rng 범위를 한칸만 내려서 잡고(=왜냐하면 헤더 데이터는 빼야하니깐)

 

rng.Clear

헤더 데이터를 뺀 모든 셀을 지워줘 ~

 

저도 vba가 처음이지만 이정도면 해볼만한 것 같습니다

 

 

다음은 셀 선택입니다.

lR = .Cells(Rows.Count, "a").End(xlUp).Row 'a열 마지막 셀의 행의 번호 보여줘

lC = .Cells(1, Columns.Count).End(xlToLeft).Column '1행 마지막 셀의 열의 번호 보여줘

 

.은 위에 With문을 통해 Sheet1이 생략되었다는 뜻입니다. (1편 참고)

Cells(Rows.Count, "a")는 "a"열의 마지막 행으로 이동하라는 뜻입니다. (=1,048,576행으로 이동해)

엑셀은 1,048,576행이 있습니다. (100만행 조금 넘는 수준밖에 되지 않습니다.)

End(xlUp)은 1,048,576행에서 Ctrl + 화살표 ↑ 과 동일합니다. (=데이터가 있는 마지막 행으로 이동) 

 

왜 이렇게 하는 걸까요? 

테이블 중간 중간에 빈 데이터가 있을 경우를 대비하기 위함입니다. 

 

열도 행과 동일한 방법으로 확인할 수 있습니다.

 

2. For To ~ Next 구문 

매크로의 핵심 ! 반복문 구문입니다.

매크로를 쓰는 이유가 반복되는 작업을 컴퓨터에게 맡기기 위함이며, 그 구문 중 하나가 바로 엑셀에서는 For To ~ Next 구문입니다.

For i = 2 To lR
    If .Cells(i, "C") = .Range("N2") Then
        '.Cells(i, "a").Resize(1, lC).Copy Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1) 'Copy & Paste
        .Cells(i, "a").Resize(1, 11).Copy Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1) 'Copy & Paste
    End If
Next

For i = 2 To lR

의 뜻은 2부터 lR변수까지 반복할꺼야 입니다.

lR은 앞에 정의하였듯, 데이터 전체 중에 마지막 행까지 라는 뜻입니다. 

예를 들어 "A1:C11" 까지인 데이터가 있을 경우, 마지막 행은 11행입니다. 

위의 경우에서 lR은 11이겠죠

 

i는 변수입니다. 

2부터 11까지 숫자가 차례로 들어간다는 뜻입니다. 

다시 말해서 i = 2, 3, 4, 5, ... 11 로 변하는 수입니다. 

 

If .Cells(i, "C") = .Range("N2") Then 

앞에서 보았던 If Then ~ End if 구문입니다. 

만약 i = 2인 경우, "C2"셀이 "N2"와 동일하다면 ~ 

 

.Cells(i, "a").Resize(1, 11).Copy Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1)

에서는 Resisze가 있네요

Resize(행, 열)입니다. 

Resize(1,11)의 경우, 행 하나를 11열까지 .Copy는 복사하겠다는 뜻입니다. 

Resize 예시

Copy 다음에 띄워쓰고 Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1) 는 내용을 그대로 붙여넣기 하겠다는 뜻입니다. (Copy & Paste)

다만 그 범위가 Sheet2.Cells(Rows.Count,"A").End(xlup).offset(1)입니다.

 

혹시 아시겠나요..? 위에서 했던 내용인데 새롭습니다.

Cells(Rows.Count,"A") : "A"열의 마지막 행으로 이동해( = =1,048,576행으로 이동해)

.End(xlUp) : Ctrl + 방향키 ↑ 로 이동해( = 데이터가 있는 곳으로 이동해)

.offset(1) 행으로 한칸 내려가 (=맨 위의 데이터는 헤더니깐 한칸 내려가)

 

그럼 이제 Sheet2의 헤더만 있고 비어있는 데이터에 조건을 만족하는 경우 데이터가 하나씩 차곡차곡 쌓일 겁니다. 

 

드디어 완성했습니다..!! 

 

모든 걸 기억하실 필요는 없고, 아 이런게 있었지 정도만 되도 나중에 찾아보실 수 있는 힘만 있으시다면 VBA로 원하는 형태의 데이터 가공은 가능하시리라 믿습니다.

 

결국 자동화를 하기 위해서는 그 전 작업이 상당한 노력을 필요로 하는 것 같습니다. 

엑셀 함수를 잘 활용하는 것, 파워쿼리를 잘 쓰는 것, VBA코드를 잘 짜는 것, Python 코드를 잘 짜는 것

쉽지 않지만 자동화 파일을 한 번 만들어두면 업무가 쉬워지더라구요

 

자동화에 한 발자국 가까워 지신 것을 축하드립니다 !