[VBA 입문] 09강. 레인지 개체(4) ㅡ 특별한 범위 선택하기

VBA

[VBA 입문] 09강. 레인지 개체(4) ㅡ 특별한 범위 선택하기

권현욱(엑셀러) 2023. 10. 2. 08:00

들어가며

레인지 개체에 접근하는 방법은 아주 다양하지만 지금까지 소개한 것과 이번 강의에서 알려드리는 것 정도만 알면 웬만한 것은 거의 이해했다고 생각해도 좋습니다. VBA에서 가장 딸린 식구가 많은 레인지 개체의 마무리를 목전에 두고 있습니다. 시작해 볼까요? 출발합니다.

이 콘텐츠는 <엑셀 VBA 파워 코딩의 정석>(권현욱 저, 디지털북스 출간)에서 편집, 인용한 것입니다.

SpecialCells 속성

셀(레인지 개체)에는 여러 종류의 정보가 들어갈 수 있습니다. 상수(Constant: 변하지 않는 수)가 들어있는 셀, 내용이 아예 들어있지 않은 셀, 수식이 입력되어 있는 셀 등등...

이 중에서 특정한 종류의 데이터가 있는 영역을 선택해야 할 경우, SpecialCells 속성을 사용합니다. 워크시트 상태에서 [홈] 탭 ㅡ [편집] 그룹 - [찾기 및 선택] - [이동 옵션]을 선택하면 [이동 옵션] 대화상자가 나타납니다. 이곳을 이용한 것과 비슷한 기능을 수행합니다.

SpecialCells 메서드의 기본적인 사용법은 다음과 같습니다.

작업 대상 영역.SpecialCells (Type, Value)

여기에서 Type, value 같은 것을 인수(argument)라고 합니다. 두 가지 인수들의 목록을 잠깐 보겠습니다. 이 많은 걸 외우려고 할 필요 없습니다. 자주 쓰는 몇 가지만 알면 됩니다. 어떤 것들이 있는지 봐두는 정도로 훑어보면 됩니다.

Type 인수

상수	셀 타입
xlCelltypeAllFormatConditions	표시 형식이 설정되어 있는 셀
xlCelltypeValidations	유효성 조건이 설정된 셀
xlCelltypeBlanks	빈 문자열이 들어있는 셀
xlCelltypeComments	메모가 들어있는 셀
xlCelltypeConstants	상수가 포함되어 있는 셀
xlCelltypeFormulas	수식이 들어있는 셀
xlCelltypeLastCell	사용된 범위 내의 마지막 셀
xlCelltypeSameFormatConditions	같은 서식을 가진 셀
xlCelltypeSameValidation	같은 유효성 조건을 가진 셀
xlCelltypeVisible	화면에 보이는(표시되어 있는) 모든 셀

Value 인수

상수	셀 타입

xlErrors	에러값
xlLogical	논리값
xlNumbers	숫자값
xlTextValues	문자열값

실제 코드를 보도록 하죠. 현재 워크시트에서 데이터가 입력되어 있는 마지막 셀을 선택하려면 이렇게 표현합니다. Cells는 시트 내의 모든 셀을 뜻하고, 그 앞에 시트 이름이 지정되지 않았으니까 현재 시트(ActiveSheet)를 작업 대상으로 합니다.

Sub specialCells()
    Cells.specialCells(xlCellTypeLastCell).Select
    MsgBox "마지막 데이터 셀 주소: " & Selection.Address
End Sub

이 코드를 실행하면 마지막 데이터 셀의 주소가 메시지 상자에 표시됩니다.

기본적인 사용법을 알았으니 응용을 해 볼까요? 수식이 들어 있는 셀(E2:F16)의 배경색을 노란색으로 칠하는 코드를 만들려면 어떻게 하면 될까요?

Sub specialCells_2()
    Dim rData As Range
    Set rData = Cells.specialCells(xlCellTypeFormulas)
    rData.Interior.ColorIndex = 6
End Sub

'수식이 들어있는 셀'에 접근하기 위한 'Type 인수'가 xlCelltypeFormulas라고 했으므로 위의 코드와 같이 됩니다.

End 속성

End 속성이라고 하니까 '마지막'과 관련된 뭔가가 아닐까, 하는 생각이 어렴풋이 들죠? 바로 그렇습니다. End는 '영역의 마지막 셀'에 접근하는 속성입니다. 현재 셀을 기준으로 상/하/좌/우 방향의 맨 끝에 있는 셀을 의미합니다. 말로 하니까 이해가 될 듯 말 듯하죠? 그럴 것 같아서 그림을 하나 준비했습니다.

이 시점에서... 수수께끼 하나 내 드릴까요? "아니, VBA 공부하다 말고 집중력 떨어지게 웬 수수께끼?"라고 하시겠지요? 다 이유가 있으니까 한번 맞춰 보세요.

집집마다 4개씩 있는데, 동네를 다 털어도 4개밖에 없고, 온 나라를 뒤져도 4개밖에 없는 것은 무엇일까~요?

정답은 '동서남북'입니다. End 속성도 동서남북과 비슷합니다. 위 그림에서 D8 셀을 기준으로, 위쪽 방향 마지막 셀은 D4, 아래쪽 마지막 셀은 D13입니다. 왼쪽으로 마지막 셀은 B8, 오른쪽으로 마지막 셀은 F8입니다.

End 속성의 인수와 워크시트에서 사용할 수 있는 단축키를 정리한 표입니다.

이동 방향	인수	단축키
위쪽 끝	End(xlUp)	Ctrl + ↑
아래쪽 끝	End(xlDown)	Ctrl + ↓
왼쪽 끝	End(xlToLeft)	Ctrl + ←
오른쪽 끝	End(xlToRight)	Ctrl + →

End 속성은 실무에서 어떤 경우에 사용될까요? 여러 가지 경우에 적용할 수 있겠지만, A열에 데이터를 계속 입력할 때 마지막으로 입력된 셀이 어디인지를 파악하는 경우에 흔히 사용됩니다.

Sub endProperty()
    Range("A10000").End(xlUp).Offset(1, 0).Select
End Sub

셀 주소는 꼭 A10000일 필요는 없고, 예상되는 충분히 큰 숫자이기만 하면 됩니다.

Offset 속성

Offset은 '참조 셀(또는 영역)을 지정한 위치로 이동'시키고자 할 때 사용하는 속성으로, 사용 규칙은 이렇습니다.

개체.Offset(행 방향 이동할 셀 수, 열 방향 이동할 셀 수)

다음 그림과 같이 A1 셀을 기준으로 행 방향으로 3행, 열 방향으로 4 열만큼 이동한 위치, 즉 E4 셀로 이동하려면 어떻게 표현할 수 있을까요?

Range("A1").Offset(3, 4).Select

간단하죠? 바로 이어서 Resize 속성까지 익힌 다음, 예제를 보도록 합니다.

Resize 속성

Resize 속성을 사용하면 선택된 셀 범위의 크기를 변경할 수 있습니다. 사용법은 간단합니다. 새로 변경할 행 수와 열 수를 지정해 주기만 하면 됩니다.

개체.Resize(행 수, 열 수)

행 수와 열 수 인수는 직접 숫자값을 지정하거나(01), 이미 선택된 영역의 행 수나 열 수를 파악한 다음, 여기에 숫자를 더하거나 빼서 표현할 수도 있습니다(02).

(01) Selection.Resize(3, 5).Select
(02) Selection.Resize(Selection.Rows.Count + 2, Selection.Columns.Count + 2).Select

이론은 이 정도면 정리가 된 것 같군요. 간단한 예제를 통해 실무에서 어떻게 쓰일 수 있는지 보도록 합시다.

Offset + Resize = ?

Offset과 Resize 속성은 따로 사용되기도 하지만 두 개가 세트로 쓰이는 경우가 많습니다. 아래 왼쪽과 같은 표를 오른쪽과 같이 팀명에는 색상을 지정해야 하는 경우가 있습니.

코딩을 시작하기에 앞서서 작업할 순서를 떠올려 봅니다.

워크시트 A1 셀의 인접 영역(CurrentRegion)에 접근한다.
행 방향으로 1행 이동한다.
제목 영역(A1:B1)은 작업 대상에서 제외한다(A2:A6 영역에 접근).
A2:A6 영역에 배경색을 지정한다.

이런 순서로 하면 되겠죠? "잘 설명된 문제는 이미 반은 해결된 문제"라는 찰스 케터링의 말이 떠오릅니다.

Sub coloringTeams()
  Dim rTbl As Range
  Set rTbl = Range("A1").CurrentRegion
  Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1).Columns(1)
  
  rTbl.Interior.ColorIndex = 6
End Sub

이 코드를 실행하면 A열에 데이터가 얼마나 있든 상관없이 제목을 제외한 영역이 노란색으로 칠해집니다. 코드를 외울 필요는 없습니다. 1 ~ 4까지의 과정을 어떻게 코드로 바꾸었는지 비교해 보는 것만으로 충분합니다.

나가며

이렇게 해서 레인지 개체에서 사용되는 어지간한 진주(속성, 메서드)를 모두 채굴했습니다. 남은 것은 캐낸 진주를 잘 꿰서 가치를 높이는 일입니다. 앞으로의 여정도 저와 함께 재미있게 탐구해 갑시다. 축하합니다.

'VBA' 카테고리의 다른 글

[VBA 입문] 11강. 실행 순서 제어(1) ㅡ 조건 분기 (0)	2023.10.04
[VBA 입문] 10강. 연산자, 변수와 상수 (112)	2023.10.03
[VBA 입문] 08강. 레인지 개체(3) ㅡ 사용된 영역에 접근하기 (92)	2023.10.01
[VBA 입문] 07강. 레인지 개체(2) ㅡ 행/열 단위로 범위 확장하기 (118)	2023.09.30
[VBA 입문] 06강. 레인지 개체(1) ㅡ Range, Cells 속성 (89)	2023.09.28

현재글[VBA 입문] 09강. 레인지 개체(4) ㅡ 특별한 범위 선택하기

권현욱의 엑셀 & IT정보