VBA 동적 범위
In this Article
이 문서에서는 Excel VBA에서 동적 범위를 만드는 방법을 설명합니다.
Excel VBA에서 특정 범위의 셀을 변수로 선언하면 해당 특정 셀에서만 작업할 수 있도록 제한됩니다. Excel에서 동적 범위를 선언하면 코드와 코드가 수행할 수 있는 기능에 대해 훨씬 더 많은 유연성을 확보할 수 있습니다.
범위 및 셀 참조하기
Excel에서 범위 또는 셀 개체를 참조할 때는 일반적으로 필요한 행과 열에 하드코딩하여 참조합니다.
Range 속성
아래 코드 예시에서 Range 속성을 사용하여 셀의 색상을 변경하거나 셀을 굵게 만드는 등의 작업을 하드코딩된 범위에서 수행할 수 있습니다.
Range("A1:A5").Font.Color = vbRed
Range("A1:A5").Font.Bold = True
Cells 속성
마찬가지로 Cells 속성을 사용하여 행과 열을 직접 참조하여 셀 범위를 참조할 수 있습니다. 행은 항상 숫자여야 하지만 열은 숫자가 될 수도 있고 따옴표로 묶인 문자가 될 수도 있습니다.
예를 들어 셀 주소 A1은 다음과 같이 표현할 수 있습니다:
Cells(1,1)또는
Cells(1, "A")Cells 속성을 사용하여 셀 범위를 참조하려면 범위의 시작과 끝을 표시해야 합니다.
예를 들어 A1: A6 범위를 참조하려면 아래 구문을 사용할 수 있습니다:
Range(Cells(1,1), Cells(1,6)그런 다음 Cells 속성을 사용하여 아래 예제 코드처럼 범위에 대한 작업을 수행할 수 있습니다:
Range(Cells(2, 2), Cells(6, 2)).Font.Color = vbRed
Range(Cells(2, 2), Cells(6, 2)).Font.Bold = True변수를 사용한 동적 범위
Excel에서 데이터의 크기가 변경되면(즉, 코딩한 범위보다 더 많은 행과 열을 사용하게 되면) 코드에서 참조하는 범위도 변경하면 유용할 것입니다. 위의 Range 객체를 사용하여 사용 중인 Excel 워크시트 영역의 최대 행과 최대 열 번호를 저장하는 변수를 만들고, 이 변수를 사용하여 코드를 실행할때마다 Range 객체를 동적으로 조정할 수 있습니다.
예제 코드는 다음과 같습니다.
Dim lRow as integer
Dim lCol as integer
lRow = Range("A1048576").End(xlUp).Row
lCol = Range("XFD1").End(xlToLeft).Column
열의 마지막 행
워크시트에 1048576개의 행이 있으므로 변수 lRow는 시트 맨 아래로 이동한 다음 End 키와 위쪽 화살표 키의 조합을 사용하여 워크시트에서 사용된 마지막 행으로 이동합니다. 이를 통해 범위에서 필요한 행의 수를 알 수 있습니다.
행의 마지막 열
마찬가지로, lCol은 워크시트의 마지막 열인 열 XFD로 이동한 다음 End 키와 왼쪽 화살표 키 조합을 사용하여 워크시트에서 사용된 마지막 열로 이동합니다. 이를 통해 범위에서 필요한 열의 번호를 알 수 있습니다.
따라서 워크시트에서 사용된 전체 범위를 가져오려면 다음 코드를 실행하면 됩니다:
Sub GetRange()
Dim lRow As Integer
Dim lCol As Integer
Dim rng As Range
lRow = Range("A1048576").End(xlUp).Row
'lRow를 사용하여 범위에서 사용된 마지막 열을 찾습니다
lCol = Range("XFD" & lRow).End(xlToLeft).Column
Set rng = Range(Cells(1, 1), Cells(lRow, lCol))
'범위를 메시지박스에 표시합니다
MsgBox "범위는 " & rng.Address
End SubSpecialCells – 마지막 셀
Range 객체의 SpecialCells 메서드를 사용하여 워크시트에서 사용된 마지막 행과 열을 가져올 수도 있습니다.
Sub UseSpecialCells()
Dim lRow As Integer
Dim lCol As Integer
Dim rng As Range
Dim rngBegin As Range
Set rngBegin = Range("A1")
lRow = rngBegin.SpecialCells(xlCellTypeLastCell).Row
lCol = rngBegin.SpecialCells(xlCellTypeLastCell).Column
Set rng = Range(Cells(1, 1), Cells(lRow, lCol))
'범위를 메시지박스에 표시합니다
MsgBox "Range is " & rng.Address
End SubUsedRange
Used Range 메서드는 현재 워크시트에서 값이 있는 모든 셀을 포함합니다.
Sub UsedRangeExample()
Dim rng As Range
Set rng = ActiveSheet.UsedRange
'범위를 메시지박스에 표시합니다
MsgBox "Range is " & rng.Address
End SubCurrentRegion
CurrentRegion은 시작 범위로 선언한 셀(즉, 아래 예제에서 rngBegin 변수)을 둘러싼 셀을 살펴본 다음 해당 선언된 셀에 ‘연결’되거나 연결된 모든 셀을 살펴본다는 점에서 UsedRange와 다릅니다. 행이나 열에 빈 셀이 발생하면 CurrentRegion은 더 이상 셀을 찾지 않습니다.
Sub CurrentRegion()
Dim rng As Range
Dim rngBegin As Range
Set rngBegin = Range("A1")
Set rng = rngBegin.CurrentRegion
'범위를 메시지박스에 표시합니다
MsgBox "Range is " & rng.Address
End Sub이 메서드를 사용하는 경우 필요한 범위의 모든 셀이 빈 행이나 열이 없이 연결되어 있는지 확인해야 합니다.
명명된 범위
코드에서 명명된 범위를 참조할 수도 있습니다. 명명된 범위는 데이터가 업데이트되거나 삽입될 때 새 데이터를 포함하도록 범위가 변경될 수 있으므로 동적범위로 볼 수 있습니다.
이 예제는 범위 이름 “1월”의 글꼴을 굵게 변경합니다.
Sub RangeNameExample()
Dim rng as Range
Set rng = Range("January")
rng.Font.Bold = = True
End Sub아래 그림에서 볼 수 있듯이 범위 이름에 행이 추가되면 해당 행을 포함하도록 범위 이름이 자동으로 업데이트됩니다.
예제 코드를 행이 추가되기 전에 실행했을때 코드의 영향을 받는 범위는 C5:C8이지만 행이 추가된 이후 다시 실행하면 코드의 영향을 받는 범위는 C5:C9가 됩니다.
테이블
코드에서 테이블을 참조할 수 있습니다(VBA에서 테이블 생성 및 조작에 대한 자세한 내용을 보려면 클릭해 주세요). Excel의 테이블 데이터가 업데이트되거나 변경되면 테이블을 참조하는 코드가 업데이트된 테이블 데이터를 참조하게 됩니다. 이 기능은 외부 데이터 소스에 연결된 피벗 테이블을 참조할 때 특히 유용합니다.
코드에서 이 표를 사용하면 각 열의 제목으로 표의 열을 참조하고 이름에 따라 열에 대한 작업을 수행할 수 있습니다. 테이블의 행이 증가하거나 감소하면 테이블 범위가 그에 따라 조정되고 코드는 테이블의 전체 데이터에 대해 작동합니다.
예제 코드는 다음과 같습니다:
Sub DeleteTableColumn()
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListColumns("Supplier").Delete
End Sub
