VBA 범위 선택하기 2탄 (Cells, Offset, Resize 조합)

 

Cells, Offset, Resize를 활용하여 엑셀의 범위를 자유자재로 선택할 수 있어요. 각 속성이 가지는 특징과 예시를 통해 범위선택이 자연스럽게 익혀지도록 연습해 보세요.

 

Cells, Offest, Resize를 활용하여 범위선택하기

 

 

 

오늘은 VBA 범위 선택하기 2탄 Cells, Offset, Resize속성을 가지고 범위를 지정해 보도록 할게요. 우선 Range로 범위 설정하기에 대한 학습이 부족하시다면 아래 링크를 방문하셔서 먼저 학습하시면 도움이 될 거예요~!

VBA 범위 선택하기 1탄 (Range)

---

VBA 범위 선택 목차

 

1. VBA 범위 선택하기 1탄 : Range로 범위 선택
2. VBA 범위 선택하기 2탄 : Cells와 Resize, Offset의 조합을 통한 범위 선택
3. VBA 범위 선택하기 3탄 : Union, Intersect을 사용한 범위 선택

4. VBA 범위 선택하기 4탄 : Special cells를 통한 범위 선택
5. VBA 범위 선택하기 5탄 : UsedRange, CurrentRegion, entirerow(column)을 통한 범위 선택
6. VBA 범위 선택하기 6탄 : Areas를 사용한 범위 선택

 

---

Cells, Offset, Resize은 어떤 역할을 할까?

Cells : 행 번호와 열 번호를 통해 Cells의 위치를 설정할 수 있는데요. Cells(2,2)는 "B2"셀로 표현할 수 있어요.

Offest : Offest은 엑셀 함수 Offest과 동일하게 사용되는데요. Offest(시작 위치, 행, 열, 행 범위, 열 범위)로 나타낼 수 있어요.

 

Offest(B2, 1,1) 설정 시, B2셀을 기준으로 아래로 한 칸, 우측으로 한칸 이동한 "C3"셀이 선택되요.

Offest(B2, 1, 1, 10, 10) 설정 시 다음과 같은 범위를 설정할 수 있어요.

 

Offest(B2, 1, 1, 10, 10)

 

 

 

Resize : 범위를 재설정하는 역할을 합니다. Cells(1,1).Resize(2,5) 설정 시 "A1:E2"의 범위를 잡을 수 있어요.

 

Cells(1,1).Resize(2,5)

 

---

Cells, Offset, Resize을 사용하여 범위를 설정해 보자.

학생수 계산을 위한 예제 테이블

 

다음은 학생수, 남학생, 여학생의 계를 구하기 위한 테이블이에요. 노란색 음영 부분에 총계가 나올 수 있도록 코딩해 보도록 할게요.

 

Sub total() Dim s As Range, boy As Range, girl As Range ' 변수 선언 / 범위를 담아두기 위해 변수타입은 Range 선언 Set s = Cells(2, 2).Offset(1, 1).Resize(8) ' 전체학생수 범위 설정 Set boy = Cells(2, 2).Offset(1, 2).Resize(8) ' 남학생 범위 설정 Set girl = Cells(2, 2).Offset(1, 3).Resize(8) ' 여학생 범위 설정 Range("c11") = Application.WorksheetFunction.Sum(s) Range("D11") = Application.Sum(boy) Range("E11") = WorksheetFunction.Sum(girl) ' 함수 사용은 전 블로그 내용 참조

 

Range범위 설정 후 Sum을 계산했던 코드와 유사한 것을 알 수 있어요. 다만 범위 설정 변수에 Cells, Offset, Resize를 사용하여 범위를 적용하였어요.

 

범위 설정 방법은 동일하니, s변수만 설명하고 넘어가도록 할게요. Cells(2,2)는 "B2"셀을 나타내며, Offset(1,1)은 "B2"에서 아래로 1칸, 우측으로 한 칸 이동하여 "C3"셀에 위치하게 돼요. 여기서 Resize(8)을 적용하면 아래로 8칸을 범위로 잡게 되는데요. 결국 C3셀을 기준으로 아래 8칸이 범위가 되는 거죠.

 

따라서 Cells(2, 2).Offset(1, 1).Resize(8)은 "C3:C10"의 범위를 설정한 것이라고 할 수 있어요.

 

---

 심화) 변수에 담아서 범위를 설정해 볼까?

 

Sub total2() Dim s As Range, boy As Range, girl As Range ' 변수 선언 / 범위를 담아두기 위해 변수타입은 Range 선언 r = Range("B2", Cells(2, 2).End(xlDown)).Rows.Count - 2 ' resize의 범위를 자동설정하기 위해 행의 숫자 카운팅 Set s = Cells(2, 2).Offset(1, 1).Resize(r) ' 전체학생수 범위 설정 Set boy = Cells(2, 2).Offset(1, 2).Resize(r) ' 남학생 범위 설정 Set girl = Cells(2, 2).Offset(1, 3).Resize(r) ' 여학생 범위 설정 Range("c11") = Application.WorksheetFunction.Sum(s) Range("D11") = Application.Sum(boy) Range("E11") = WorksheetFunction.Sum(girl) ' 함수 사용은 전 블로그 내용 참조 Range("C11:E11").ClearContents '해당 범위 내용 삭제(서식 유지) End Sub

 

 

앞에서 살펴본 코드를 토대로 변수만 추가한 코드입니다. 조금 더 복잡해 보이죠? 기존 사용했던 범위 설정에서 Resize 할 위치를 변수 S에 담아서 표현하였는데요. 변수의 장점은 코드를 간략하게 만들 수 있고, 코드 수정 시 해당 변수만 수정하여 적용할 수 있는 편의성을 가지고 있어요.

 

r변수에 사용된 Range("B2", Cells(2, 2).End(xlDown)).Rows.Count-2는 데이터가 포함된 행을 카운팅 하는 코드예요.

사용된 행의 수에서 "구분"과 "전교생"으로 표현된 행의 수를 빼기 위해 -2를 넣어주었어요.  자동 범위 선택 시 유용하게 사용되는 코드이니, 사용방법을 알아두시면 유용하게 사용될 수 있죠. (자동 범위 설정의 경우 별도 챕터로 다룰 예정입니다. 이런 코드가 있다 정도만 숙지하고 넘어가 주세요.) 그 외에는 1번 코드와 유사한 내용이니 다음으로 넘어가도록 할게요.

 

Sub total3() Dim s As Range, boy As Range, girl As Range ' 변수선언 / 범위를 담아두기 위해 변수타입은 Range 선언 r = Range("B2", Cells(2, 2).End(xlDown)).Rows.Count - 2 ' resize의 범위를 자동설정하기 위해 행의 숫자 카운팅 t = Application.Match("학생수", Range("C2:E2"), 0) 'match함수를 통해 전체학생수 범위설정 시작위치 설정 b = Application.Match("남학생", Range("C2:E2"), 0) 'match함수를 통해 남학생 범위설정 시작위치 설정 g = Application.Match("여학생", Range("C2:E2"), 0) 'match함수를 통해 여학생 범위설정 시작위치 설정 Set s = Cells(2, 2).Offset(1, t).Resize(r) Set boy = Cells(2, 2).Offset(1, b).Resize(r) Set girl = Cells(2, 2).Offset(1, g).Resize(r) ' 위에서 설정한 변수를 통한 적용 Range("c11") = Application.WorksheetFunction.Sum(s) Range("D11") = Application.Sum(boy) Range("E11") = WorksheetFunction.Sum(girl) ' 함수사용은 전 블로그 내용 참조 Range("C11:E11").ClearContents '해당범위 내용 삭제(서식 유지) End Sub

 

무엇이 달라졌을까요?? Resize에 들어갈 숫자를 Match함수를 통해 변수에 담아주었는데요. 이렇게 하면 행이 늘어나도 카운트하여 동적 범위로 적용해 줄 수 있겠죠? (VBA함수 부분도 별도로 다룰 예정이오니, 범위 설정하는 방법에 집중해주세요.^^)

 

---

마치며

지금까지 Cells, Offset, Resize를 통한 범위 설정법에 대해 알아보았는데요. 처음엔 잘 이해가 안 되실 수 있으나, 몇 번 사용해보시면 엑셀 함수와 크게 다르지 않다고 느끼실 거예요. 직장인 여러분의 칼퇴를 응원하며, VBA 범위 선택하기 3탄, Intersect, Union 그리고 Area을 통한 범위 선택으로 다시 찾아뵐게요.

 

▼▼▼함께 보면 좋은 정보▼▼▼

VBA 범위 선택하기 6탄! Areas를 사용한 범위 선택!!

 

VBA 범위 선택하기 5탄 (UsedRange, CurrentRegion, entirerow)

 

VBA 범위 선택하기 4탄 (Specialcells 사용법)

 

VBA 범위 선택하기 3탄 (Union, Intersect 사용법)

 

VBA 범위 선택하기 1탄 (Range)

 

지금 투자하는 시간은 나를 자유롭게 만드는 시간이다.