Skip to content

조회 수 1612 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
오늘은 셀 조작 3번째 시간이고 혹시라도 Range 개체에 대해서 부족한 부분은 응용편에서 다뤄 볼 것입니다.

초보이신 분들이 자주 묻는 질문 중에 '마지막 데이터가 있는 셀이 어디냐'라는 질문이 있습니다.
이 문제에 대해서 과거에 엑셀 유저들의 무척 많은 논란과 연구가 있었습니다.

결론적으로 아주 애매한 질문이죠.
왜냐~ 만약에 시트에 그림과 같이 A3와 C2셀에 데이터가 있다고 가정하면 A3셀을 마지막 셀로 잡을 것인지, C2셀을 마지막을 잡을 것인지, 오른쪽 끝열 C와 아래쪽 끝행 3을 기준으로 데이터가 없는 C3셀을 마지막 셀로 잡을 것인지, 아주 애매합니다.



보통 위와 같이 질문하는 질문자의 의도는 아래 그림에서 홍길동, 김길동, 강가딘 다음에 데이터를 추가시키거나 마지막의 강가딘을 읽어와야 하는데 데이터 개수가 가변적이니 코딩으로 마지막 데이터의 위치를 구하고 싶다는 것이죠.



위와 같은 작업을 할 때는 보통 기준열을 잡으시는 것이 제일 방어적인 코딩이 됩니다.

위의 테이블에서 주소하고 전화번호의 특정 데이터들은 B4 셀 처럼 생략될 수는 있지만 이름 데이터들은 보통은 생략될 수 있는 형태가 아니므로 A열, 이름 필드를 기준열로 잡으시면 되겠습니다. A열은 4행까지 데이터가 있고 B열은 3행까지 데이터가 있는데 결과 데이터의 마지막 행은 4로 나오는 것이 보편적인 생각이겠죠.

이런 경우 수작업으로는 워크시트에서 A65536 행을 선택하고 Ctrl + (위화살표) 를 하시면 A열의 마지막 데이터가 있는 셀을 선택하게 됩니다.
한번은 수작업으로 직접해보시기 바랍니다.

이를 코딩으로 구현하면
Range("A65536").End(xlUp).Select 입니다.

전 시간의 내용을 섞어서 응용을 해보겠습니다.
A열의 마지막 셀 '강가딘' 데이터의 다음 위치인 A5셀을 선택하게 만들고 싶다면 다음과 같이 하시면 됩니다.

Range("A65536").End(xlUp).Offset(1, 0).Select
또는
Range("A65536").End(xlUp)(2).Select

xlUp은 엑셀 상수입니다. vbRed처럼 vb가 붙은 것은 VB에서 쓸 수 있는 상수이고 xl이 붙은 것은 엑셀에서 쓸 수 있는 상수입니다.
전에 말씀드렸지만 이것은 엑셀 개체를 참조해야 이것을 쓸 수가 있지 CreateObject 만으로는 쓰실 수 없습니다.

어쨌든 Range("A65536")는 A열의 마지막 셀(65536은 2의 16승)이고 Range 개체입니다.
Range("A65536").End(xlUp)도 Range 개체입니다. End(xlUp)는 상위 Range 개체에서 Ctrl + (위화살표)가 됩니다.

End(xlDown) 아래 화살표, End(xlToLeft)는 좌측, End(xlToRight)는 우측을 가리킵니다.(좌우측은 To 가 붙음에 유의하시기 바랍니다.)

위 그림에서는 Range("A1").End(xlDown)으로 코딩 해도 동일한 위치가 됩니다만 A열에 데이터가 하나도 없거나 연속되지 않는 경우 문제가 되니 End(xlUp)을 쓰시는 것이 더 방어적인 코딩이 되겠습니다.
데이터가 없다면 Range("A65536").End(xlUp).Select 라고 하시면 A1이 선택이 됩니다.

이번에는 응용해서 B열을 기준으로 해서 마지막 데이터가 있는 셀의 선택이 아니라 단순히 마지막 행이 몇 행인지 알고 싶다면 Row 속성을 이용하십시오.
Msgbox Range("B65536").End(xlUp).Row

B열을 기준으로 한 것이니 3이 표시가 될 것입니다.

Range("IV3").End(xlToLeft).Select 라고 응용하면 3행을 기준으로 마지막 셀이 선택이 되겠습니다. IV는 256 번째 맨 우측 열 이름을 나타냅니다. 워크시트에서 확인해 보시기 바랍니다.

참고로 제가 셀 조작 1편에서 CurrentRegion, UsedRange도 Range 개체라고 말씀드린 적이 있습니다.
직접실행창에서 해보시기 바랍니다.

? Range("A1").CurrentRegion.Address
$A$1:$C$4

? Activesheet.UsedRange.Address
$A$1:$C$4


위에서 본 것과 같이 CurrentRegion의 상위 개체는 Range 개체이고 UsedRange 의 상위개체는 시트개체입니다.
동일한 결과가 나왔지만 미묘하게 틀립니다.
이 내용도 위에서 설명드린 End 개체와 마찬가지로 제대로 설명 드리려면 엄청난 분량입니다.

아주 간단히만 설명드리겠습니다.
CurrentRegion는 MSDN은 설명을 묘하게 해놨지만 좀 쉽게 설명드리자면 상위개체(위에서는 Range("A1"))를 기준으로 데이터가 있으면 범위를 가로, 세로, 대각선 방향으로 범위를 확장시켜 나가다가 주변 셀에 데이터가 없으면 더 이상 범위를 확장시키지 못하고 그 해당영역까지만을 돌려줍니다. 지뢰찾기 게임을 해보셨다면 주변에 깃발이 몇 개 있는지 따지게 되는 것과 비슷한 원리입니다. 위의 그림 C4셀에서 더이상 오른쪽이나 아래로 범위를 확장시키지 못합니다.

MSDN에서 UsedRange는 지정한 워크시트에서 사용된 범위를 나타낸다고 되어있는데 주의할 것은 문서가 저장이 되지 않으면 과거의 범위를 가져오는 경우가 있습니다.

End 개체와 UsedRange, CurrentRegion 는 아주 비슷한 구석도 많고 활용도도 높을 수 있지만 특히 UsedRange, CurrentRegion 는
주의 깊게 쓰셔야 하고 왜 제가 End 를 쓰시는 것이 방어적인 코딩인가에 대해서는 약간의 감은 잡으셨을 것 같습니다.

CurrentRegion 을 좀 잘 알고 싶으면 이기춘님의 VBA 강좌를 참고하십시오.
http://www.excellove.com/board/bbs/view.php?id=vba&page=2&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=99

A열의 데이터가 연속으로 있는 경우 Range("A1").CurrentRegion.Rows.Count 라고 하시면 A열의 데이터의 개수를 구할 수 있습니다.
데이터가 연속되어 있지 않다면 생각과는 다른 결과를 가져올 수가 있습니다.
연속되어 있는 것에 관계 없이 COUNTA라는 워크시트 함수로 A열(1열) 데이터의 개수를 구할 수 있습니다.
WorksheetFunction.CountA(Columns(1))

WorksheetFunction도 전에 설명드렸지만 워크시트 함수를 VBA에서 쓰기 위한 것이고 앞에 Application 이 생략된 형태입니다.
WorksheetFunction 에 대한 자세한 사항은 아래 링크를 참고하십시오.
http://www.excellove.com/board/bbs/view.php?id=vba&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=105

참고로 SpecialCells(xlCellTypeLastCell) 이라는 것이 있는데 위의 내용과 거리가 멀고 xlCellTypeLastCell 라는 것은 코딩 실무에서는 많이 활용되지 않습니다.
SpecialCells 은 워크시트에서 편집메뉴-이동, 바로가기 키(단축키)로는 F5와 동일합니다.

Sub KITest9()
    Range("B2").Value = "데이터1"
    Range("D10").Value = "데이터2"
    Range("D10").Clear
    MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
    MsgBox ActiveSheet.UsedRange.Address
End Sub


위의 예에서는 UsedRange 는 B2 셀의 주소를 가져오는데 반해 SpecialCells(xlCellTypeLastCell)은 지워진 D10셀의 주소를 가져옵니다.
개인적으로는 마지막 셀의 정보(셀 값, 행번호 등)를 구할 때는 VBA 코드 뿐만 아니라 함수로 구하는 것도 상당히 중요하다고 생각합니다.
이 부분은 실제로 코딩하는 응용편에서 설명드리겠습니다.
VBA를 많이 접하신 분들도 Range 개체 사용법이 생각하시는 것보다 그렇게 간단하지 않습니다.

위 소스 중에 Clear가 쓰였는데 셀을 지우는 방법 중에 많이 쓰이는 Range 개체의 메서드로는
Delete, Clear, ClearContents 가 있습니다.

Delete 는 삭제입니다. 서식까지 다 지워지고 뒤에 Shift 라는 인수를 사용하게 되는데 이 인수에는 xlShiftToLeft 또는 xlShiftUp 를 쓸 수 있습니다.
Delete 는 삭제 후에 주변의 셀로 다시 채워지게 되는데 오른쪽에 있는 데이터가 왼쪽으로 이동될 때는 xlShiftToLeft 를, 아래의 있는 내용이 위로 올라오게 할 때는 xlShiftUp 를 쓰시면 됩니다.
저는 보통은 생략해서 코딩합니다. Rows(1).Delete 하고 인수를 생략하면 첫번째 줄이 삭제되고 2행 부터의 아래방향의 데이터들이 1행으로 올라오게 됩니다.
Columns(3).Delete 하면 1~2열은 변함이 없고 3열의 내용이 삭제되고 4열부터 우측열의 데이터들이 3열부터로 위치가 변하게 되죠.
제가 말은 장황하게 썼지만 이 부분은 해보시면 쉽습니다.

Clear도 Delete 와 비슷하게 생각하시면 되지만 Delete와 다르게 열 너비, 행 높이가 디폴트 값으로 되지 않고 그대로 유지되며 삭제 후에 데이터들의 위치도 바뀌지 않습니다.
그래서 모든 셀을 초기화로 돌릴 때는 Cells.Clear 가 아닌 Cells.Delete 로 하셔야 합니다.

ClearContents 는 워크시트에서 키보드로 Delete 키를 누르는 것과 마찬가지로 데이터는 지워지나 서식은 지워지지 않습니다. 예를 들어 노란색 셀이다 그러면 지우더라도 노란색이라는 정보는 남아있게 됩니다.

비슷한 예제로는 ClearFormats(서식 지우기), ClearComments(메모 지우기) 등이 있습니다.
사용방법은 위와 동일하니 설명은 생략하고 지우기가 몇 개 더 있지만 활용도가 높지 않을 것 같아서 생략하고 ClearContents, Delete 메서드에 대하여 다음 강좌도 참고하십시오.
http://www.excellove.com/board/bbs/view.php?id=vba&page=2&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=101

어쨌든 위에서 열거한 메서드들을 사용하다보면 병합된 셀에서 병합된 셀의 일부분을 변경할 수 없다고 메세지가 나오면서 지워지지 않는 경우가 있습니다.



말 그대로 일부분만 지울려고 하는 경우이니 그 때는 현재 위치에서 MergeArea 로 병합된 범위를 잡아준 후 Delete, Clear, Clearcontents 를 쓰시면 됩니다.

Range("A1").MergeArea.Clearcontents 'A1~A2가 병합이 된 셀인 경우, Range("A1").MergeArea 와 Range("A1:A2") 는 동일

상황에 따라 미묘한 차이는 있겠지만 MergeArea 를 쓰지않고 Range("A1").Value = "" 해도 별로 다를 것은 없습니다.
Range("A1").MergeArea 라는 것은 A1셀을 기초로 하여 MergeArea 로 다시 주변에 병합된 셀까지를 Range 개체로 만들어 주는 역할을 합니다. 그래서 그렇게 쓸 수 있는 것이죠.

병합에 관계된 내용으로 예제를 만들어봤습니다.

Sub KITest10()
    Cells.Delete
'시트 내에 전체 셀 삭제
    Range("A1:C1").Value = "ABC"

    Application.DisplayAlerts = False
'경고창 무시
    Range("A1:C1").Merge
'A1~C1 셀 병합
    Application.DisplayAlerts = True

    If Range("A1").MergeCells Then
'A1셀이 병합이 되어 있다면
        MsgBox "A1 셀이 병합이 되어 있습니다."
    End If

    If Not Application.Intersect(Range("A1:B10"), Range("A5")) Is Nothing Then
'A1:B10 범위와 A5 의 교집합 범위가 존재한다면
        MsgBox "A5셀은 A1:B10 범위에 속해있습니다."
    End If
End Sub


필요한 부분만 설명드리자면 Application.DisplayAlerts = False 문장을 빼시면 다음과 같은 메세지가 나오기 때문에 자동화에 거슬리는 구문이므로 위와 같이 코딩해야 됩니다.



Range("A1:C1").Merge 는 A1~C1 의 셀을 병합한다는 뜻입니다.
Range("A1").MergeCells 는 병합의 유무입니다. True 이면 Range("A1")이 병합된 상태이고 False 이면 그렇지 않은 경우입니다.

If Not Application.Intersect(Range("A1:B10"), Range("A5")) Is Nothing Then 구문은 원래 Union 에서 설명드렸어야 하는 것인데 Union 과 반대개념으로 Intersect 를 쓰실 수 있습니다. Union 은 합집합, Intersect 는 교집합입니다. VBA 코딩으로는 상위 개체 Application은 생략하실 수 있습니다. Range("A1:B10") 와 Range("A5") 가 공유되는 교집합이 없다면 Nothing 을 반환합니다. 위 소스는 앞에 Not 붙었으므로 Nothing 이 아니면, 의역을 하자면 'Range("A5") 가 Range("A1:B10") 범위에 속한다면'으로 해석할 수 있습니다. 위와 같은 형태는 아니지만 rngTmp 라는 Range 개체 변수를 둬서 'rngTmp가 어떤 범위에 속한다면' 이라는 If 문 형태로 많이 써먹을 수 있는 내용입니다.

Intersect 메서드에 자세한 사항은 다음 링크를 참고하십시오.
http://www.excellove.com/board/bbs/view.php?id=vba&page=2&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=98

오늘 말씀 드린 내용을 정리를 하자면 End(xlUp) 은 꼭 기억하도록 하시고 UsedRange, CurrentRegion 는 어느 정도 숙달이 되시면 사용하도록 하십시오. 그리고 데이터 지우는 것과 병합에 대해서 알아봤습니다.

다음 시간에는 매크로 기록기를 다룰 것입니다. 지금까지 말씀드린 내용을 기초지식으로 하여 매크로를 기록하고 효율적으로 수정하고 응용하는 것에 초점을 맞춰서 진행하도록 하겠습니다. 그럼 다음시간에 뵙겠습니다.
?

List of Articles
번호 카테고리 제목 글쓴이 날짜 조회 수
» Excel VB로 Excel(엑셀) 핸들링 - 셀 조작 3 165 콩이아빠 2009.11.09 1612
21 Excel 확장자가 자동으로 대문자로 바뀌는 현상... 67 콩이아빠 2009.08.05 1297
20 Excel 각시트의 원하는 셀값 가져오기 1 77 콩이아빠 2009.08.05 1276
19 Excel 다수의 동일 문자의 옆셀에 위치한 내용을 함수를 써서 취합하려는데.. 1 10 콩이아빠 2009.08.05 986
18 Excel 범위내 조건을 만족하는 가장 큰 값 뽑아내기 1 127 file 콩이아빠 2009.08.12 732
17 P/C 시작프로그램(msconfig)에 프로그램 등록하는 법 - 2 1 콩이아빠 2010.03.01 700
16 P/C 숨겨진 파일 및 폴더 보이게(설정) 하기 1 콩이아빠 2010.02.19 583
15 P/C 비스타 시작프로렘 폴더 1 콩이아빠 2010.02.19 561
14 P/C 시작프로그램(msconfig)에 프로그램 등록하는 법!! 40 콩이아빠 2010.02.20 539
13 Excel 숨겨져있는 쉬트는 제외하고 탭이름 가져오기 51 콩이아빠 2009.08.05 535
12 Excel 특정셀의 문자를 파일이름으로 저장하는 메크로... 콩이아빠 2009.08.05 377
11 Excel myvlookup2함수 file 콩이아빠 2009.08.05 366
10 Excel 시트선택 메크로에서 시트이름 코드 29 file 콩이아빠 2009.08.05 362
9 P/C 비스타 시작및 실행프로그램 콩이아빠 2010.02.19 358
8 Excel 저장 메크로 실행에서...무조건 '예'로 강제 자동 실행시킬려면... 27 콩이아빠 2009.08.05 283
7 Excel 탭의 이름들을 한 sheet에 취합하여 정열 시킬려면 어떻게 해야 하나요? 1 콩이아빠 2009.08.05 257
6 P/C 비스타 네트워크 프린트 설정 방법 file 콩이아빠 2010.02.18 246
5 Excel 저장 메크로 실행에서...무조건 '예'로 강제 자동 실행시킬려면... 콩이아빠 2009.08.06 243
4 Excel 파일을 불러왔다 닫는 메크로 실행에서..... 1 콩이아빠 2009.08.05 224
3 Excel 시트이름을 바꾸면 메크로 코드에서도 바뀌게 할려면? 콩이아빠 2009.08.05 221
2 Excel 특정셀의 문자를 파일이름으로 만드는 메크로 콩이아빠 2009.08.05 189
1 Excel 자동으로 메크로 실행하기.... 1 콩이아빠 2009.08.11 169
Board Pagination Prev 1 Next
/ 1

Korea NO.1 VISION 2020 / Designed by HJS_Sketch

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소