| |
워크시트의 데이터가 입력된 마지막 행을 찾는 방법
-----------------------------------------------
1) UsedRange 속성
Sheets("clu").UsedRange 속성은, 데이터가 입력된 적이 있었던
셀들의 전체 범위를 반환합니다.
따라서
Sheets("clu").UsedRange.Row + Sheets("clu").UsedRange.Rows.Count
' 해당 UsedRange의 시작 행번호 + 행 수
를 계산하면, 마지막 행번호를 알 수 있겠죠.
그러나 UsedRange는 글자 그대로 Used 즉, 과거에 얾메이는 한계가 있습니다.
언젠가 한 번이라도 데이터를 가졌던 적이 있는 셀은
비록 지금은 그 데이터가 지워지고 빈 셀 즉, 사용되지 않는 셀이라고 하더라도
UsedRange에는 소속되어 버립니다.
2) CurrentRegion 속성
기준이 되는 셀을 중심으로 상하좌우 방향으로 데이터가 연속되어 있는 영역을
반환합니다.
이 속성은 과거에 연연하지는 않지만,
데이터가 연결이 되어 있는 상황에서만 제대로 작동합니다.
마치 바둑 돌처럼, 어딘가가 끊어진 부분이 있어서 사석이 되어버리면
CurrentRegion에서는 쫓겨나지요.
3) End 속성
이 속성은 XlDirection이라는 방향을 나타내는 인수를 지정하여 사용합니다.
즉, xlUp, xlDown, xlToLeft, xlToRight의 네 방향으로
이 이름들은 엑셀의 내장 상수(Enumeration)로 정의되어 있습니다.
보통 워크시트에서 Ctrl 키를 누른 상태에서 화살표 키를 눌렀을 경우
그 셀로부터 누른 화살표 키가 가리키는 방향으로
연속하여 데이터가 입력된 영역의 경계점 셀로
껑충 뛰는 것 아시나요?
예를 들면 B3:B8까지는 뭔가 데이터가 입력되어 있고(그 사이는 빈 셀 없음)
B9:B12까지는 비어 있는 셀이고, B13에 다시 데이터가 있다고 할 때
B3 셀을 선택한 상태에서 Ctrl 누른 상태로 아래쪽 화살표를 누를 때마다
B3 -> B8 -> B13 로 셀 포인터가 껑충 껑충 뛰는 것 말이지요.
마찬가지로 VB 코드에서
Cells(3,2).End(xlDown).Select
라고 명령하면 B8 셀이 선택되고
Cells(10,2).End(xlDown).Select
라고 명령하면 B13 셀이 선택됩니다.
사실은, 실제로도 매크로 기록을 켠 상태에서 Ctrl+화살표키로 이동한 후에
기록된 VB 코드를 확인해 보면 End 속성을 이용한 것을 볼 수 있답니다.
한편, Application.Rows.Count는 워크시트의 최대 가능한 행 수를
Application.Columns.Count 최대 가능 열 수를 알려줍니다.
Application 개체 지정은 생략해도 됩니다.
만일, 워크시트의 데이터 구조상 A열이 색인키로서
어딘가에 데이터가 있으면 그 행의 A열 셀에 반드시 데이터가 존재하고
A열에 데이터가 비어 있는 행에는 다른 열에도 일체의 데이터가 없는
그런 규칙이 있다면
Cells(Rows.Count, 1)은 A열의 맨 마지막 행 셀(xlsx 파일인 경우 A1048576)이고
Cells(Rows.Count, 1).End(xlUp)은
거기서부터 위쪽으로 연속 빈 셀을 모두 지나 최초로 데이터를 만나는 셀
그러니까 A열에서 데이터가 입력되어 있는 가장 아래의 셀을 가리키게 됩니다.
따라서 Cells(Rows.Count, 1).End(xlUp).Row 값을 조사하면
데이터의 마지막 행 번호를 알 수 있지요.
다시 말하지만, 이 방법으로 마지막 행을 계산하려면
앞에 말했듯이
워크시트의 데이터 구조상 A열이 색인키로서
어딘가에 데이터가 있으면 그 행의 A열 셀에 반드시 데이터가 존재하고
A열에 데이터가 비어 있는 행에는 다른 열에도 일체의 데이터가 없는
그런 규칙이 꼭 성립해야 합니다.
4) SpecialCells 메소드
이 메소드도 End 속성처럼 뒤에 인수로 지정하는 값에 의해서
특별한 셀 개체를 반환해 줍니다.
그 인수는 XlCellType이라는 상수 집합(Enumeration)으로서
xlCellTypeLastCell 외에 9가지(총 10가지)가 있습니다.
자세한 사항은 MS사에서 제공하는 온라인 도움말을 참고하세요.
- SpecialCells 메소드 https://msdn.microsoft.com/ko-kr/library/office/ff196157(v=office.14).aspx
- XlCellType https://msdn.microsoft.com/ko-kr/library/office/ff836534(v=office.14).aspx
이 강좌도 대부분 도움말을 요약 번역한 것입니다.
아무튼 그 상수들 중에 가장 많이 활용되는 것이 xlCellTypeLastCell입니다.
역시 매크로 기록기능을 켠 상태에서
워크시트에서 Ctrl+End 키를 눌러 시트의 맨 마지막 셀(우하단)을 선택하고
기록된 VB 코드를 보면
ActiveCell.SpecialCells(xlLastCell).Select
이 보입니다.
이 셀의 Row, Column 등 속성을 조사해보면
현재 활성시트의 데이터 크기를 알 수 있겠지요.
다만, 이 때 계산되는 마지막 셀이란
사실은 앞에 설명한 UsedRange의 마지막 셀에 해당합니다.
다시 말해서, 지금은 데이터가 지워지고 없어도
언젠가 데이터를 가졌던 적이 있는 셀들은 모두
사용된 범위(UsedRange)로 인식해버리는 거품 문제를
여전히 갖는다는 것이지요.
5) UsingRange 사용자정의 함수
이와 같은 UsedRange 속성의 문제점을 개선하여
루아흐뉴마님의 도움으로
UsingRange 사용자정의 함수를 제가 공동개발한 적이 있습니다.
다음 링크에 말이지요.
http://www.officetutor.co.kr/board/Dtype/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123840&page=&fchk=&fval
그런데, 이번 질문의 답변을 준비하면서 실험하다가
이 함수에도 문제가 발견되어서 다음과 같이 수정하였습니다.
문제는 아예 비어 있는 시트(아직 아무 데이터도 갖지 않은)에 대해
이 함수를 적용하면 런타임오류가 난다는 것입니다.
이 질문의 답변이 늦어진 이유도
그 원인을 찾느라고 좀 헤맸기 때문이고요.
========================================================================
이제 질문하신 문제를 개선된 UsingRange 함수로 깔끔하게 해결할 수 있습니다.
------------------------------------------------------------------------
Function UsingRange(Optional ByRef firstRow As Long, Optional ByRef firstCol As Long, Optional ByRef lastRow As Long, Optional ByRef lastCol As Long) As Range
On Error GoTo EmptySheet
firstRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlNext).Row
firstCol = Cells.Find(what:="*", searchorder:=xlByColumns, searchdirection:=xlNext).Column
lastRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
lastCol = Cells.Find(what:="*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
Set UsingRange = Range(Cells(firstRow, firstCol), Cells(lastRow, lastCol))
Exit Function
EmptySheet:
firstRow = 1
firstCol = firstRow
lastRow = firstRow
lastCol = firstRow
Set UsingRange = Cells(firstRow, firstCol)
End Function
Sub 복제()
Dim srx As Long
Dim scx As Long
Dim r As Long
Dim c As Long
Dim dr As Long
Sheets("clu").Activate
UsingRange lastRow:=srx, lastCol:=scx
Sheets("ProcessPage").Activate
UsingRange lastRow:=dr
With Sheets("clu")
For r = 10 To srx
dr = dr + 1
For c = 1 To scx
Cells(dr, c).FormulaR1C1 = .Cells(r, c).FormulaR1C1
Next c
Next r
End With
End Sub
|
|
엑셀에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
