매크로 작업 중에는 이미 입력되어 있는 데이터들을 가공하여 계산을 한 값을 특정 열에 삽입해야하는 작업이 있을 수 있다. 이런 경우 작업을 할 범위를 VBA 코드에 Range 형태로 지정할 수 도 있겠지만, 입력하는 데이터양에 변화가 있는 상황이라면 매번 VBA 코드를 바꿔야할 것이다. 따라서 데이터가 있는 셀의 범위를 알아내는 과정 또한 VBA 코드로 작성해둘 필요가 있다.
사람이 엑셀을 통해 데이터가 있는 셀의 범위를 찾는다고 하면 바로 답이 나올 것이다. 예를들어 다음과 같은 엑셀 시트에서 데이터가 있는 셀의 범위를 알아내는 작업을 사람이 한다고 생각해보자.
데이터가 들어 있는 엑셀 시트
사람이 데이터가 있다고 인지하는 영역
사람의 경우 눈에 보이는 셀에 대해 데이터의 포함 유무를 파악하여 데이터가 있는 셀의 범위를 정할 수 있다. 이러한 작업은 데이터가 많아진다고 해도 동일하게 이뤄질 수 있다. 즉 특정 영역에 있는 모든 셀의 값을 확인하여 데이터가 있는지 없는지를 확인하는 방법이다. 그런데 Office 2016 기준으로 Excel의 행의 수는 1,048,576 개 이며, 열의 수는 16,384 개이다. 즉, 이러한 작업을 위해 살펴봐야할 셀의 수는 17,179,869,184개가 된다. 이 모든 셀을 다 비교하는 루틴을 VBA에 넣을 수도 있겠지만, 본 포스트에서는 다른 방법을 소개하려고 한다.
- (1) UsedRange 사용
다행히도 VBA에서는 시트에 데이터가 포함되어 있는 셀을 모두 포함하는 최소 크기의 Range 객체를 반환해주는 키워드인 UsedRange를 제공한다. 위에서 다룬 예제에 적용하면 다음과 같은 영역을 반환하게 된다.
UsedRange의 이러한 특성을 활용하여 데이터가 있는 셀의 범위를 구할 수 있을 것이다.
- (2) End(Ctrl) + 방향키 사용
엑셀을 실행 한 후에 A1 셀을 선택 한 후에 END키 + 아래 방향키(↓)를 눌러보자. 다음 그림처럼 데이터가 있는 가장 마지막 셀로 이동하는 것을 알 수 있다. END키 대신 CTRL 키를 사용해도 똑같이 동작하며, 4개의 방향키에 대해 같은 작업을 수행할 수 있다.
A1 선택
END + 아래방향키
또한 엑셀의 행과 열의 최대 값은 정해져 있어서 B열의 맨 마지막 행인 B1048576에서 END+위방향키(↑)를 누르면 B8접근하는것이 가능하다. 이 방법으로 각각의 행 및 열에서 데이터가 어느셀까지 있는지를 개별적으로 알아내는 것이 가능하다.
B1048576 선택
END + 위방향키: B8에 접근
XFD3 선택
END + 왼쪽방향키: E3에 접근
02. VBA에서 필요한 도구들
위에서 소개한 두가지 방법을 이용하여 범위를 구하기 위해 몇몇 키워드를 알아야 한다.
- Range.End 속성
Range객체 이후에 End(XlDirection) 형태로 사용할 수 있다.
XlDirection에 해당하는 값들은 다음과 같다.
이름
설명
xlDown
방향 (↓)
xlToLeft
방향(←)
xlToRight
방향(→)
xlUp
방향(↑)
사용 예) Range("B4").End(xlUp).Select // B4셀에서 END + 방향키(↑)를 눌렀을 때의 셀을 선택한다.
- WorkSheet.UsedRange 속성
워크 시트(엑셀의 시트) 에서 데이터가 있는 영역을 표기하는 Range 객체를 반환한다.
사용 예) WorkSheet("Sheet1").UsedRange.Select // 워크시트 Sheet1의 데이터가 있는 영역*을 반환한다. *데이터가 있는 영역을 모두 포함하는 가장 작은 직사각형 형태의 셀의 범위
