Range.AutoFilter Method(4) - SpecialCells(xlCellTypeVisible)(1)

필터로 조건을 걸면 조건에 해당되지 않는 행은 보이지 않고 조건에 맞는 행만 보이기 때문에 SpecialCells(xlCellTypeVisible)으로 VBA에서 데이터를 처리해야 합니다.

 

1. SpecialCells(xlCellTypeVisible)

SpecialCells(xlCellTypeVisible)란 홈 탭 > 편집 그룹에 있는 찾기 및 선택을 누르고, 아래에서 이동 옵션을 누르면 나오는

 
이동 옵션 중 '화면에 보이는 셀만'에 해당합니다.

 
나머지 이동 옵션은 아래와 같습니다.

이름	값	설명
xlCellTypeAllFormatConditions	-4172	조건부 서식 - 모두
xlCellTypeAllValidation	-4174	데이터 유효성 - 모두
xlCellTypeBlanks	4	빈 셀
xlCellTypeComments	-4144	메모
xlCellTypeConstants	2	상수
xlCellTypeFormulas	-4123	수식
xlCellTypeLastCell	11	마지막 데이터 셀
xlCellTypeSameFormatConditions	-4173	조건부 서식 - 조건 일치
xlCellTypeSameValidation	-4175	데이터 유효성 - 조건 일치

 

2. 필터링된 첫 번째 데이터로 이동하기

가. Offset 속성 이용하기

서울시 지역 시간별 수질 현황4.xlsm

0.17MB

 

 

 

아래와 같이 동명을 가락1동으로 제한하면 가락1동이 278행에 있는데, 동명에서 아래 화살표키를 누르면 엑셀에서는 278, 695행 등으로 화면에서 보이는 대로 아동 합니다.

 
그러나, B1셀에 커서가 있는 상태에서 매크로로 아래 화살표키를 누르는 것처럼 한 줄 아래로 이동하도록 아래와 같이 코드를 작성한 후 매크로를 실행하면

Sub move_down()
'    ActiveCell.Offset(1, 0).Select
    'ActoveCell이 B1셀이 아닐 수 있어서 B1셀로 고정(2023.08.03 수정)
    Range("b1").Offset(1, 0).Select
End Sub

 
B278로 이동하는 것이 아니라 바로 아래 셀인 B2셀로 이동합니다.

보이는 셀이 아니라 한 줄 아래 셀(B2)로 이동

 

나. While과 Hidden 속성 이용하기

While문을 이용해서 숨겨진 셀이면 이동을 반복하고, 보이는 셀이면 멈추도록 할 수 있습니다.

Sub move_down2()
    Range("b2").Select
    While Selection.EntireRow.Hidden = True
        ActiveCell.Offset(1, 0).Select
    Wend
End Sub

 
(코드 설명)
Range("b2").Select : B2셀로 이동
While Selection.EntireRow.Hidden = True : 선택된 셀의 전체 행이 숨김인 동안, 다시 말해 선택된 셀의 행이 보이지 않는다면 While문을 반복합니다.
ActiveCell.Offset(1, 0).Select : 한 줄 아래로 이동합니다.

(매크로 실행)
위 매크로를 실행하면 B278셀로 이동하고, $B$278이라고 메시지를 보여줍니다.

보이는 셀의 처음으로 이동

 
그러나 B2917셀에 '아무동'이라고 입력하고 아무동으로 필터링한 후 위 매크로를 실행하면 한 줄 한 줄 이동하면서 조건을 검증하기 때문에 시간이 오래 걸립니다.

한 줄로 이동함에 따라 시간이 오래 걸림

따라서, 한 줄씩 이동해서 보이는 셀을 찾는 것이 아니라 바로 보이는 셀로 이동하도록 해야 합니다.
 
 

다. SpecialCells(xlCellTypeVisible) 이용하기

(1) 보이는 셀 주소 알아내기
그러기 위해서는 먼저 보이는 셀의 주소를 알아야 합니다.

Sub move_down3()
    Range("b2").Select
    MsgBox Selection.SpecialCells(xlCellTypeVisible).Address
End Sub

 
(코드 설명)
Range("b2").Select : B2셀로 이동
MsgBox Selection.SpecialCells(xlCellTypeVisible).Address : B2셀을 기준으로 보이는 셀의 주소를 표시합니다.
(매크로 실행)
위 매크로를 실행하면 $1:$1,$2917:$1048576이라고 보이는 셀의 주소를 절대주소 형식으로 표시합니다.

보이는 셀의 주소(1:1)

셀 주소가 1행이 있고, 그다음은 2917부터 맨 아래줄까지입니다.
주소가 절대주소 형식이므로 Address를 Address(0, 0)으로 수정합니다.
그리고, 동명을 개포1동으로 변경한 후 실행하면 1:2,419:419,836:836,1253:1253,1669:1669,2085:2085,2501:2501,2918:1048576로서 위와 달리 1행과 2행이 연속되므로 1:1이 1:2로 되어 있습니다.

보이는 셀의 주소(1:2로 다름)

 
(2) 보이는 셀 주소를 배열에 넣기
Microsoft 365에서 Split함수가 생겨서 ,를 기준으로 분리하면 됩니다. Microsoft 365가 아니라면 (4)를 참고 바랍니다.

Sub move_down3()
    Dim arr_address() As String
    Range("b2").Select
'    MsgBox Selection.SpecialCells(xlCellTypeVisible).Address(0, 0)
    arr_address = Split(Selection.SpecialCells(xlCellTypeVisible).Address(0, 0), ",")
End Sub

 
(3) 보이는 셀 중 처음으로 이동하기
arr_address 배열을 살펴보기 위해 arr_address 줄 왼쪽 막대를 클릭해서 중단점을 설정하고 실행한 후 F8키를 누르고, 지역창에서 arr_address의 주소를 살펴보면 아래와 같이 1:2, 419:419식으로 들어가 있습니다.

지역 창에서 배열의 값 확인 가능

 
보이는 첫 번째 셀은
첫 번째 배열의 값이 1:1이라면 2번째 배열의 첫 번째 숫자에 해당하는 행에 있고,
1:다른 숫자라면 첫 번째 배열의 다른 숫자 행에 있습니다.
 
(코드 수정)

Sub move_down3()
    Dim arr_address() As String, first_addr() As String, second_addr() As String
    Dim goto_addr As Integer
    Range("b2").Select
'    MsgBox Selection.SpecialCells(xlCellTypeVisible).Address(0, 0)
    arr_address = Split(Selection.SpecialCells(xlCellTypeVisible).Address(0, 0), ",")
    first_addr = Split(arr_address(0), ":")
    If first_addr(1) = 1 Then
        second_addr = Split(arr_address(1), ":")
        goto_addr = Val(second_addr(0))
    Else
        goto_addr = 2 'Val(first_addr(1))
    End If
    MsgBox goto_addr
End Sub

 
first_addr() As String, second_addr() As String : 0번째와 1번째 인덱스에 해당하는 arr_address를 콜론 기준으로 분리해서 저장하기 위한 배열, first_addr과 second_addr을 문자열  형식으로 선언합니다.
 
Dim goto_addr As Integer : 보이는 첫 번째 행을 담을 변수 goto_addr을 정수 형식으로 선언합니다.
 
first_addr = Split(arr_address(0), ":") : first_addr에 arr_address의 0번째 인덱스 값을 콜론으로 분리해서 저장합니다. 
  중단점을 설정한 후 first_addr 배열의 값을 확인해 보니 1과 2가 큰따옴표로 둘러싸여 있으니 문자열(String)입니다.

 

(2023.08.03 수정) 1:8, 식인 경우 8행이 아니라 2행으로 이동해야 하므로 두번째 goto_addr값을 2로 수정

If first_addr(1) = 1 Then         second_addr = Split(arr_address(1), ":")         goto_addr = Val(second_addr(0))     Else         goto_addr = 2 'Val(first_addr(1))     End If

 
first_addr의 인덱스 1에 해당하는 값(순번으로는 두 번째 값)이 1이라면, 문자열인데 숫자 1과 비교해도 에러가 나지 않습니다. 이 경우는 1:1인 경우로 두 번째 arr_address에서 값을 찾아야 하므로
 
second_addr = Split(arr_address(1), ":") : arr_address(1)을 콜론으로 분리한 후 second_addr에 저장하고,
 
goto_addr = Val(second_addr(0)) : goto_addr에 second_addr의 0번째 인덱스 값을 저장합니다.
 
else : first_addr의 인덱스 1에 해당하는 값(순번으로는 두 번째 값)이 1이 아닌 경우, 예를 들어 1:2와 같은 경우입니다. 이 때는 first_addr의 1번째 인덱스 값을 가져오면 되므로
 
goto_addr = 2 'Val(first_addr(1))이라고 해서 값을 가져오는데, 문자열이므로 Val함수를 이용해 숫자로 바꿉니다. 그런데 Val를 빼고 해 보니 문제없이 잘됩니다.

(2023.08.03 수정) 2인 경우는 문제가 없는데 3이상인 경우 2행이 아닌 3행 등으로 이동하는 문제점이 있어 수정
 
MsgBox goto_addr : 그러면 1과 2열이 연속되므로 2란 값이 표시됩니다.
 
(2행이 보이는 경우의 실행)
2행이 보이면 2란 값이 메시지 상자에 표시됩니다.

보이는 셀의 첫번째 행 값 반환

 
셀 주소가 필요하므로 MsgBox goto_addr range("b"&goto_addr).select로 수정합니다. 그러면 B2셀로 이동합니다.
 
(2행이 보이지 않는 경우 실행)
동명을 아무동으로 변경한 후 실행하면 순식간에 B2917셀로 이동합니다.

2행이 보이지 않을 경우 arr_address의 두번째 배열에서 행값을 가져 옴

 
(4) 보이는 첫 번째 셀로 이동하기(Microsoft 365가 아닌 경우)

Sub move_down4()
    Dim arr_address As String
    Dim first_colon As Integer, first_comma As Integer, second_colon As Integer
    Dim first_addr As String
    Dim goto_addr As Integer
    
    Range("b2").Select

    arr_address = Selection.SpecialCells(xlCellTypeVisible).Address(0, 0)
    first_colon = InStr(arr_address, ":")
    first_comma = InStr(arr_address, ",")
    second_colon = InStr(first_colon + 1, arr_address, ":")
    
    first_addr = Mid(arr_address, first_colon + 1, first_comma - first_colon - 1)
    If first_addr = 1 Then
        goto_addr = Mid(arr_address, first_comma + 1, second_colon - first_comma - 1)
    Else
        goto_addr = 2 'Mid(arr_address, first_colon + 1, first_comma - first_colon - 1)
    End If
    Range("b" & goto_addr).Select
End Sub

 
Split함수가 없기 때문에 inStr함수를 이용했습니다. inStr함수는 문자열에서 특정 문자의 위치를 찾아주는 반환해 주는 함수입니다.
 
구문은 InStr([시작위치, ] 검색대상 문자열, 검색 문자열 [, 비교 방법])입니다.
 
로직은 첫 번째 콜론 다음의 숫자가 1이면 콤마 다음의 숫자를 가져오면 되고,
1이 아니면 콜론 다음의 숫자를 가져오는 것입니다.
 
배열을 이용하지 않기 때문에 변수명은 같지만 배열에서 변수로 변경했습니다.
 
arr_address = Selection.SpecialCells(xlCellTypeVisible).Address(0, 0) : 보이는 셀의 주소를 문자열 변수 arr_address에 저장합니다.
first_colon = InStr(arr_address, ":") : 보이는 셀의 주소에서 첫 번째 콜론의 위치를 first_colon(정수)에 저장합니다.
first_comma = InStr(arr_address, ",") : 보이는 셀의 주소에서 첫 번째 콤마의 위치를 first_comma(정수)에 저장합니다.
second_colon = InStr(first_colon + 1, arr_address, ":") : 두 번째 콜론은 첫 번째 콜론의 위치 다음부터(+1) 콜론을 찾아 위치를 second_colon(정수)에 저장합니다.
 
first_addr = Mid(arr_address, first_colon + 1, first_comma - first_colon - 1) : 1:2 등 첫 번째 셀 주소에서 콜론 다음부터 콤마 전까지를 가져와서 first_addr(문자열)에 저장합니다. 1:2인 경우는 2가 저장됩니다.
 
If first_addr = 1 Then : first_addr, 다시 말해 첫 번째 콜론 다음에 있는 숫자가 1이면
    goto_addr = Mid(arr_address, first_comma + 1, second_colon - first_comma - 1) : 콤마 다음의 첫번째 숫자를 goto_addr(정수)에 저장합니다.
 
Else : first_addr이 1이 아닌 경우
    goto_addr = 2 'Mid(arr_address, first_colon + 1, first_comma - first_colon - 1) : 첫번째 콜론 다음부터 첫번째 콤마 위치 전까지 숫자를 goto_addr(정수)에 저장합니다.

(2023.08.03 수정) 2인 경우는 문제가 없는데 3이상인 경우 2행이 아닌 3행 등으로 이동하는 문제점이 있어 수정
 
코드는 좀 더 길어졌지만 '아무동'인 경우 B2917셀로 빠르게 이동합니다.

 

아래는 2023.08.03 수정한 파일입니다.

서울시 지역 시간별 수질 현황(완성4).xlsm

0.17MB