[VBA] Tips - 필터링 후 row 갯수 구하기

엑셀 프로그래밍 추가 작업을 하고 있는데 몇가지 새로운 로직이 나왔습니다.

필터링 후 row의 갯수를 구하는 것과 필터링 후 특정 필드의 Average를 구하는 것입니다.

우선 필터링 후 row의 갯수를 구하는 것은 아래와 같이 하면 됩니다.

Range.SpecialCells(xlCellTypeVisible).Rows.Count

이걸 제 작업에서는 아래와 같이 활용했습니다.

Sub Sub이름()

    Dim firstCell As Range
    Dim lastCell As Range
    Dim lastRow As Double
    Dim rngtest As Range
    Dim count As Integer

    Call clickLink("링크")

    ''''''' get the first Cell to calculate
    Set firstCell = getBelowCell2("필드이름1")
   
    ''''''' get the last row to calculate
    lastRow = activateLastrow2Cal2("필드이름2")
    
    ''''''' get the last Cell to calculate
    Set lastCell = firstCell.Offset(lastRow - firstCell.Row, 0)
   
    Call filterOption("필드이름3", "1")
   
    ' Get count of rows
    Set rngtest = Range(firstCell, lastCell)
   
    count = rngtest.SpecialCells(xlCellTypeVisible).Rows.Count
   
    Call writeTable4("Sheet Name", 0, 0, count, "G6")

End Sub

특정 Sheet로 가서 필터링 한 후 특정 필드의 row 갯수를 구하는 겁니다.

우선 제가 작업하는 엑셀 파일에는 첫 sheet 에 링크가 있어서 원하는 sheet로 링크를 클릭해서 가도록 돼 있습니다.

Sub clickLink(link As String)
    Call goToSheet("링크가 있는 sheet name")
    Worksheets("링크가 있는 sheet name").Range(link).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    On Error Resume Next
        ActiveSheet.ShowAllData
    On Error GoTo 0
End Sub

이게 clickLink() 함수 인데요.

Link가 있는 Cell 주수를 String 파라미터로 받습니다. 그리고  goToSheet() 로 링크가 있는 sheet name을 파라미터를 전달합니다.

Sub goToSheet(sheetName As String)
    Sheets(sheetName).Select
    Range("B4").Select
End Sub

goToSheet()은 sheetName을 파라미터로 전달 받아서 해당 Sheet로 이동을 하는 함수 입니다.

다시 clickLink()를 보면 goToSheet() 다음에 해당 링크를 클릭합니다.

간단하게 말하면 clickLink()에 링크를 전달하면 그것을 클릭하는 겁니다.

그 다음에 getBelowCell2() 에서 값을 전달받아 firstCell Range에 담는데요.

Public Function getBelowCell2(cellVal As String) As Variant
    Call ActivateCell(cellVal)
    Set getBelowCell2 = ActiveCell.Offset(1, 0)
End Function

getBelowCell2 는 필드 이름(String)을 전달 받아서 바로 그 밑의 Cell을 return 합니다.

두번째는 activateLastrow2Cal2()에서 return 받은 Double형을 lastRow에 담습니다.

Public Function activateLastrow2Cal2(cellVal As String) As Double
    Call getBelowCell(cellVal)
    activateLastrow2Cal2 = ActiveCell.End(xlDown).Row
End Function

activateLastrow2Cal2()는 필드이름을 파라미터로 전달 받아서 그 필드의 가장 마지막 row 번호를 return 합니다.

이 값을 가지고 계산할 필드의 마지막 Row를 계산합니다.

Set lastCell = firstCell.Offset(lastRow - firstCell.Row, 0)

그러면 계산할 필드의 첫번째 Cell과 마지막 Cell을 확보한 겁니다.

그 다음에 필터링을 하는데요.

Sub filterOption(cellVal As String, filterVal As String)
    ActivateCell ("필드이름")
    'ActivateCell.Offset(0, -1).Select

    If ActiveSheet.FilterMode Then

    Else: Rows(ActiveCell.Row).Select
        Selection.AutoFilter
        Selection.AutoFilter
    End If
    
    Call ActivateCell(cellVal)
    ActiveCell.AutoFilter Field:=ActiveCell.Column, Criteria1:=filterVal
End Sub

Sub ActivateCell(cellVal As String)
    Cells.Find(What:=cellVal, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
End Sub

우선 ActivateCell은 필드 이름을 전달 받아서 해당 필드이름(제목)이 있는 Cell을 활성화 시키는 겁니다.

filterOption()은 필터링할 필드 이름과 필터링할 값을 파라미터로 받아서 필터링을 하는 겁니다.

그 전에 IF 문에 로직을 만들었는데 그건 제가 작업하는 엑셀 파일에서 이렇게 해야 되서 넣은 겁니다.

이러면 계산할 필드의 첫번째와 마지막 Cell을 구했고 조건에 맞게 필터링도 했습니다.

    Set rngtest = Range(firstCell, lastCell)
   
    count = rngtest.SpecialCells(xlCellTypeVisible).Rows.Count
   
    Call writeTable4("Sheet Name", 0, 0, count, "G6")

그 다음에 나오는 소스코드 입니다.

우선 첫번째 cell 에서 마지막 cell 까지를 rngtest라는 Range에 담고 이 range의 row 수를 count 에 담습니다.

writeTable4()는 전달하는 Sheet Name 의 해당 위치(위에서는 G6) 에 count 값을 write 하려고 만든 겁니다.

Sub writeTable4(sheetName As String, firstNum As Integer, secondNum As Integer, resultVal As Integer, writeCell As String)
    Dim writeVal1 As Range
    Sheets(sheetName).Select
    Set writeVal1 = Range(writeCell).Offset(firstNum, secondNum)
    writeVal1.Value = resultVal
    Range("D6").Select
End Sub

그 내용을 보면 전달받은 sheet name을 가진 sheet를 활성화 시키고 전달받은 위치 ("G6") 에 전달받은 값을 write 하는 겁니다.

writeVal1에 offset()을 사용한 것은 조건에 따라서 write할 Cell의 위치("G6") 를 바꾸기 위해서 입니다.

count = rngtest.SpecialCells(xlCellTypeVisible).Rows.Count

이 부분을 정리하려고 했는데 이전에 만들어 놨던 여러 Component들까지 다 살펴 봤네요.