VBA (Excel) - 필터링 한 후 합계 구하기

지난번 사용한 테이블에다 Option1 과 Option2 필드를 추가했습니다.

위 그림처럼 Option1 의 0.00 으로 필터링 한 후 Aug 필드의 합계를 구하겠습니다.

    Dim seqRng As Range
    Dim option2Rng As Range
    Dim firstRow As Long
    Dim lastRow As Long

며칠 전에 만든건데 지금와서 소스를 보니까 저도 생소하네요.

일단 Seq 필드와 option2 필드 Range를 담을 변수를 만들었습니다.

제 기억으로는 Option2는 사용되질 않는데 아마 Option1을 담을 Range인데 타이핑을 잘 못 했나 봅니다.

그리고 합계 구할 Row 의 범위를 알기 위해 firstRow와 lastRow를 Long 타입의 변수로 정의했습니다.

   Range("B4").Select
   
    ' Link Click
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
   
    On Error Resume Next
        ActiveSheet.ShowAllData
    On Error GoTo 0
   
    Cells.Find(What:="State", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
   
    ActiveCell.End(xlDown).Activate
   
    lastRow = ActiveCell.Row

첫번째 줄은 그냥 아무 셀이나 Select 한 거구요.

그 다음을 보니까 링크를 클릭했습니다. 이건 지난번에 만들었던 소스코드에도 그대로 나오는 겁니다. 이렇게 하면 링크 걸린 페이지로 가겠죠? 링크 걸린 페이지는 위에 보이는 테이블이 있는 페이지 입니다.

그 다음은 일단 이전에 작업할 때 필터링이 된게 있을 지도 모르니까 이 필터링 된 걸 풀고 모든 값을 보이도록 하는 코드 입니다.

그런데 필터링이 안 돼 있으면 이 부분에서 에러가 나니까 이 에러를 나지 않도록 처리도 했습니다.

다음은 State 라는 값을 가진 Cell을 찾아서 Activate 시키고 여기서 쭉 밑으로 데이터가 있는 마지막 Cell 까지 값니다.

그리고 그 셀의 Row를 lastRow에 담습니다.

   Cells.Find(What:="Seq", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
       
    Set seqRng = ActiveCell
   
    Cells.Find(What:="Option2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
       
    Set option2Rng = ActiveCell
   
    Cells.Find(What:="Option1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

    ActiveSheet.Range(seqRng, option2Rng).AutoFilter Field:=option2Rng.Column - seqRng.Column, Criteria1:="0.00"
   
    Cells.Find(What:="Aug", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Offset(1, 0).Activate
       
    firstRow = ActiveCell.Offset(1, 0).Row

이 부분은 fistRow를 구하는 부분까지 잘랐는데 뭔 코드가 이렇게 긴지 모르겠네요.

한번 볼까요?

우선 Seq 라는 값을 가진 셀을 찾아서 그 셀을 seqRng에 담습니다. (이건 왜 만들었을까? 지금 봐서는 잘 이해가 안 가네요.)

하여간 그 다음에는 Option2로 검색해서 그것을 option2Rng에 담습니다.

이것도 왜 담는건지 모르겠네요.

다음은 Option1이라는 값을 가진 Cell을 찾아서 그 Cell을 Activate 시킵니다.

앗 잠깐만요...

그 아래에 필터링 하는 부분이 있군요.

거기서 seqRng와 option2Rng가 사용됩니다.

괜히 그 값을 구해서 변수에 담은게 아니었군요.

이렇게 되면 Option1을 Find로 찾아서 Activate 시킨 부분은 아무 의미 없는 코딩 같은데요.

한번 계속 볼까요?

일단 필터링이 있는 범위를 Range로 정하고 나서 몇번째 필드를 필터링 할 건지 알려주고 필터링 할 값은 0.00으로 정해 줍니다.

이렇게 되면 Option1 필드 중 0.00을 가진 놈들로만 필터링이 됩니다.

다음에 하는 일은 Aug라는 값을 가진 셀을 찾아서 그 아래 셀을 Activate 시킵니다.

여기까지 보니까 확실하게 Option1이라는 값을 가진 셀을 Find 한 부분은 필요가 없는 부분이란게 확실 하네요.

이제 Aug셀의 바로 아래 Row를 firstRow로 지정했습니다.

이렇게 되면 firstRow와 lastRow가 모두 구해 졌습니다.

    ActiveCell.Offset(lastRow - ActiveCell.Row, 0).Activate

    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[" & firstRow - lastRow & "]C:R[-1]C)"
   
    MsgBox firstRow - lastRow

    Selection.Copy
   
    Sheets("New_Sheet").Select
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C5").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Aug Filter 0"
    Range("D8").Select

첫번째 줄은 Aug 필드의 맨 마지막 Row 바로 밑을 Activate 시킨 겁니다.

거기다가 구한 합계를 넣기 위해서죠.

그 밑에 줄에서 SUBTOTAL을 사용하는데요.

이 함수는 필터링된 값만 가지고 합계를 내기 위해 사용했습니다.

그 안에 9는 합계를 낼거라는 의미이구요.

끝에 R[-1]C는 현재 Activate 된 셀의 바로 윗줄을 말하는 겁니다.

Aug 컬럼 중 합계를 구할 맨 밑줄이 되죠.

그 바로 전에는 Aug 셀의 바로 밑셀을 구하는 겁니다.

Aug 셀의 바로 밑셀부터 맨 밑셀까지를 정해 준거구요. 그 값들을 SUBTOTAL로 합계를 내게 됩니다.

그러면 그 합계가 맨 위에 있던 테이블 그림 처럼 Aug 필드의 맨 밑에 있는 셀 바로 밑에 표시 됩니다.

그 다음에는 그 값을 Copy 하구요. New_Sheet로 가서 적당한 위치에 복사한 값을 붙여넣기를 합니다.

이제 이정도 까지만 하면 제가 업무 하면서 만들고 싶었던 매크로를 만들 수 있겠네요.

필요한 기술은 다 Research 한 것 같습니다.

저는 이제 제가 필요한 매크로를 만들어야 겠습니다.

처음에는 새로운 Sheet를 만들었고 그 다음에는 특정 필드의 값들의 합계를 구했고 이번에는 필터링한 후 특정 필드의 값들의 합을 구했습니다.

지금까지 만든 소스코드는 Research 목적으로 만든거라서 실제 사용하려면 Refactoring 과정을 거쳐야 합니다.

불필요한 코드들은 없애고 좀 더 간단하게 만들 수 있는 로직은 간단하게 고치고 반복적으로 사용되는 로직은 따로 Component 를 만들어서 사용할 수 있도록 고치고 등등이요.

혹시 이 Refactoring 과정을 거치지 않은 소스코드라도 참고하시고 싶으신 분들을 위해서 제가 작업한 파일을 아래에 올려 놓을께요.

ForTesting.xlsm