Excel VBA 범위와 셀

VBA의 범위와 셀

Excel 스프레드시트는 셀에 데이터를 저장합니다. 셀은 행과 열로 배열됩니다. 각 셀은 행과 열의 교차점으로 식별할 수 있습니다(예: B3 또는 R3C2).

Excel 범위는 하나 이상의 셀을 나타냅니다(예: A3:B4).

셀 주소

A1 표기법

A1 표기법에서 셀은 열 문자(A부터 XFD까지)와 행 번호(1부터 1,048,576까지)로 지칭됩니다. 이를 셀 주소라고 합니다.

VBA에서는 Range 객체를 사용하여 모든 셀을 참조할 수 있습니다.

' 현재 활성 시트의 B4셀을 참조합니다
MsgBox Range("B4")

' 'Data'로 이름지어진 시트에서 B4셀을 참조합니다
MsgBox Worksheets("Data").Range("B4")

' 'MyData' 통합문서의 'Data' 시트에서 B4셀을 참조합니다
MsgBox Workbooks("My Data").Worksheets("Data").Range("B4")

R1C1 표기법

R1C1 표기법에서 셀은 R+행 번호, C+열 번호로 표시됩니다. 예를 들어 R1C1 표기법에서 B4는 R4C2로 표기합니다. VBA에서는 Cells 객체를 사용할때 R1C1 표기법을 사용합니다:

' R[6]C[4] 셀은 D6셀을 의미합니다
Cells(6, 4) = "D6"

셀의 범위

A1 표기법

두 개 이상의 셀을 참조하려면 시작 셀 주소와 마지막 셀 주소 사이에 “:”를 사용합니다. 다음은 A1에서 D10까지의 모든 셀을 참조합니다:

Range("A1:D10")

R1C1 표기법

둘 이상의 셀을 참조하려면 시작 셀 주소와 마지막 셀 주소 사이에 “,”를 사용합니다. 다음은 A1에서 D10까지의 모든 셀을 참조합니다:

Range(Cells(1, 1), Cells(10, 4))

셀에 값 입력하기

셀 또는 인접한 셀 그룹에 값을 쓰려면 범위를 참조하고 = 기호를 넣은 다음 저장할 값을 쓰면 됩니다:

' F6를 F6셀에 입력합니다
Range("F6") = "F6"

' E6를 [6]C[5] 셀 즉 E6 셀에 입력합니다.
Cells(6, 5) = "E6"

'  A1:D10 범위에 A1:D10 을 입력합니다
Range("A1:D10") = "A1:D10"
' 또는
Range(Cells(1, 1), Cells(10, 4)) = "A1:D10"

셀에서값 읽어오기

셀에서 값을 읽으려면 값을 저장할 변수를 참조하고 = 기호를 넣은 다음 읽을 범위를 참조하면 됩니다:

Dim val1
Dim val2

' F6 셀에서 값 읽어오기
val1 = Range("F6")

' E6 셀에서 값 읽어오기
val2 = Cells(6, 5)

MsgBox val1
Msgbox val2

참고: 여러 셀 범위의 값을 저장하려면 배열을 사용해야 합니다.

비연속적인 셀

인접하지 않은 셀을 참조하려면 셀 주소 사이에 쉼표를 사용합니다:

' A1, A3, A5 셀에 10을 입력합니다
Range("A1,A3,A5") = 10


' A1:A3와 D1:D3에 10을 입력합니다
Range("A1:A3, D1:D3") = 10

셀의 교차점

인접하지 않은 셀을 참조하려면 셀 주소 사이에 공백을 사용합니다:

' A1:D10 과 D1:F10 사이의 공통 지점인 D1:D10에 Col D를 입력합니다
Range("A1:D10 D1:G10") = "Col D"

셀 또는 범위에서 오프셋

Offset 함수를 사용하면 지정된 범위(셀 또는 셀 그룹)에서 지정된 행 수 및 열 수만큼 참조를 이동할 수 있습니다.

Offset 구문

Range.Offset(행의_수, 열의_수)

셀에서 오프셋하기

' A1 셀로부터 오프셋하기
' 셀 자체를 참조합니다
' 0행 0열만큼 이동합니다.
Range("A1").Offset(0, 0) = "A1"

' 1행 0열만큼 이동합니다.
Range("A1").Offset(1, 0) = "A2"

' 0행 1열만큼 이동합니다.
Range("A1").Offset(0, 1) = "B1"

' 1행 1열만큼 이동합니다.
Range("A1").Offset(1, 1) = "B2"

' 10행 5열만큼 이동합니다
Range("A1").Offset(10, 5) = "F11"

범위에서 오프셋하기

' 범위 A1:D4에서 4행 4열만큼 참조를 이동하기
' 새로운 참조는 E5:H8 입니다
Range("A1:D4").Offset(4,4) = "E5:H8"

범위에 참조 설정하기

Range 변수에 범위를 할당하려면: Range 유형의 변수를 선언한 다음 Set 명령을 사용하여 범위로 설정합니다. RANGE는 객체이므로 SET 명령을 사용해야 합니다:

' Range 변수를 선언합니다
Dim myRange as Range

' 변수를 범위 A1:D4로 설정합니다
Set myRange = Range("A1:D4")

' $A$1:$D$4를 출력합니다
MsgBox myRange.Address

범위 크기 재설정하기

Range 객체의 Resize 메서드 는 참조 범위의 크기를 변경합니다.

Dim myRange As Range

' 범위 크기를 재설정합니다
Set myRange = Range("A1:F4")

' $A$1:$E$10를 출력합니다
Debug.Print myRange.Resize(10, 5).Address

크기가 조정된 범위의 왼쪽 상단 셀은 원래 범위의 왼쪽 상단 셀과 동일합니다.

Resize 구문

Range.Resize(행의_수, 열의_수)

OFFSET vs Resize

Offset은 범위의 치수를 변경하지 않고 지정된 행과 열 수만큼 이동합니다. Resize는 원래 범위의 위치는 변경하지 않지만 지정된 행과 열 수만큼 치수를 변경합니다.

시트의 모든 셀

The Cells 객체 는 시트의 모든 셀을 참조합니다 (1048576 행, 16384 열).

' 시트의 모든 셀 지우기
Cells.Clear

UsedRange

UsedRange 속성은 활성 시트의 왼쪽 위 사용 셀에서 오른쪽 아래 사용 셀까지 직사각형 범위를 제공합니다.

Dim ws As Worksheet
Set ws = ActiveSheet

'활성시트에서 B2셀이 값이 입력된 첫번째 셀이고 L4셀이 값이 입력된 마지막 셀일 경우 $B$2:$L$14 
Debug.Print ws.UsedRange.Address

CurrentRegion

CurrentRegion 속성은 참조된 셀/범위가 포함된 왼쪽 위 셀에서 오른쪽 아래 셀까지 연속적으로 사용된 셀에 대해서 범위를 제공합니다.

Dim myRange As Range

Set myRange = Range("D4:F6")

' D4:F16, B2, L14 셀에 값이 채워져 있다면 $D$4:$F$16을 출력합니다
Debug.Print myRange.CurrentRegion.Address

' 시작점을 단일 셀로 참조할 수도 있습니다

Set myRange = Range("D4") 

' $D$4:$F$16을 출력합니다
Debug.Print myRange.CurrentRegion.Address

Range 속성

아래와 같이 주소, 셀의 행/열 번호, 범위 내 행/열 수를 가져올 수 있습니다:

Dim myRange As Range

Set myRange = Range("A1:F10")

' $A$1:$F$10을 출력합니다
Debug.Print myRange.Address

Set myRange = Range("F10")

' 범위의 마지막행인 10을 출력합니다
Debug.Print myRange.Row

' 범위의 마지막열인 6을 출력합니다
Debug.Print myRange.Column

Set myRange = Range("E1:F5")
' 범위에서 행의 수인 5를 출력합니다
Debug.Print myRange.Rows.Count

' 범위에서 열의 수인 2를 출력합니다
Debug.Print myRange.Columns.Count

시트의 마지막 셀

시트의 마지막 셀을 가져오려면 셀 개체와 함께 Rows.Count 및 Columns.Count 속성을 사용할 수 있습니다:

' 마지막 행 번호인 1048576 를 출력합니다
Debug.Print "Rows in the sheet: " & Rows.Count

' 마지막 열 번호인 16384를 출력합니다
Debug.Print "Columns in the sheet: " & Columns.Count

' 마지막 셀의 주소인 $XFD$1048576 를 출력합니다
Debug.Print "Address of Last Cell in the sheet: " & Cells(Rows.Count, Columns.Count)

특정 열에서 마지막으로 사용된 행

END 속성은 범위의 마지막 셀을 가져오고 End(xlUp)은 마지막 셀부터 시작해서 위쪽 방향에서 가장 먼저 사용된 셀을 가져옵니다.

Dim lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

특정 행에서 마지막으로 사용된 열

Dim lastCol As Long

lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

END 속성은 범위의 마지막 셀을 가져오고 End(xlToLeft)는 마지막 셀에서 처음 사용된 셀로 왼쪽으로 이동합니다.

xlDown 및 xlToRight 속성을 사용하여 현재 셀의 첫 번째 아래쪽 또는 오른쪽에서 사용된 셀로 이동할 수도 있습니다.

Cell 속성

공통 속성

다음은 일반적으로 사용되는 셀 속성을 표시하는 코드입니다.

Dim cell As Range
Set cell = Range("A1")

cell.Activate
Debug.Print cell.Address
' $A$1을 출력합니다

Debug.Print cell.Value
' 456을 출력합니다(셀에 입력된 값)
' Address

Debug.Print cell.Formula
' 셀에 입력된 수식 =SUM(C2:C3) 을 출력합니다

' 노트(메모)에 입력된 값을 불러옵니다
Debug.Print cell.Comment.Text

' 셀의 스타일을 불러옵니다
Debug.Print cell.Style

' 셀의 서식을 불러옵니다
Debug.Print cell.DisplayFormat.NumberFormat

Cell 폰트

Cell.Font 객체에는 셀 글꼴의 속성이 포함되어 있습니다:

Dim cell As Range

Set cell = Range("A1")

' 보통, 기울임꼴, 굵게, 굵은 기울임꼴
cell.Font.FontStyle = "굵은 기울임꼴"
' 위 코든느 아래 두 줄의 코드와 의미가 같습니다
cell.Font.Bold = True
cell.Font.Italic = True

' Set font to Courier
cell.Font.FontStyle = "Courier"

' 글자 색을 설정합니다
cell.Font.Color = vbBlue
' RGB로 색을 설정할 수도 있습니다
cell.Font.Color = RGB(255, 0, 0)

' 글자 크기를 설정합니다
cell.Font.Size = 20

복사 및 붙여넣기

모두 붙여넣기

범위/셀을 한 위치에서 다른 위치로 복사하여 붙여넣을 수 있습니다. 다음 코드는 소스 범위의 모든 속성을 대상 범위로 복사합니다(CTRL-C 및 CTRL-V와 동일).

'단순한 복사
Range("A1:D20").Copy 
Worksheets("Sheet2").Range("B10").Paste

'현재 시트에서 Sheet2로 복사하기
Range("A1:D20").Copy destination:=Worksheets("Sheet2").Range("B10")

선택하여 붙여넣기

선택하여 붙여넣기 옵션을 사용하여 원본 범위의 선택된 속성을 대상에 복사할 수 있습니다:

' 값만 붙여넣기
Range("A1:D20").Copy
Worksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues

붙여넣기 옵션에 사용할 수 있는 옵션은 다음과 같습니다:

' 선택하여 붙여넣기 옵션 유형
xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

컨텐츠에 자동 맞춤

자동 맞춤을 사용하여 행과 열의 크기를 내용 길이에 맞게 변경할 수 있습니다:

' Change size of rows 1 to 5 to fit contents 
Rows("1:5").AutoFit

' Change size of Columns A to B to fit contents 
Columns("A:B").AutoFit

더 많은 Range 예제

엑셀에서 필요한 작업을 수행하는 동안 매크로 기록 기능을 사용하는 것도 VBA를 사용하기 위한 좋은 방법입니다. 사용 가능한 다양한 옵션과 사용 방법을 이해하는 데 도움이 됩니다.

For Each

아래와 같이 For Each 구문을 사용하여 범위를 쉽게 반복할 수 있습니다:

For Each cell In Range("A1:B100")
    ' 셀에서 특정 작업을 하도록 코드를 작성합니다
Next cell

반복할 때마다 범위 중 셀 하나가 변수 cell에 할당되고 해당 셀에 대해 For 루프 내에 작성된 구문이 실행됩니다. 모든 셀이 처리되면 반복문이 종료됩니다.

Sort

Sort는 Range 객체의 메서드입니다. Range.Sort에 정렬 옵션을 지정하여 범위를 정렬할 수 있습니다. 아래 코드는 C2 셀의 키를 기준으로 A:C 열을 정렬합니다. 정렬 순서는 xlAscending 또는 xlDescending이 될 수 있습니다. 첫 번째 행이 머리글 행인 경우 Header:= xlYes를 사용해야 합니다.

   Columns("A:C").Sort key1:=Range("C2"), _
      order1:=xlAscending, Header:=xlYes

Find

Find는 Range 객체의 메서드입니다. 이 메서드는 검색 조건과 일치하는 콘텐츠가 있는 첫 번째 셀을 찾아 해당 셀을 Range 객체로 반환합니다. 일치하는 항목이 없으면 Nothing을 반환합니다.

다음(이전) 항목을 찾으려면 FindNext 메서드(또는 FindPrevious)를 사용해야합니다.

다음 코드는 범위에서 “John”으로 시작하는 모든 셀의 글꼴을 “Arial Black”으로 변경합니다:

For Each c In Range("A1:A100")
    If c Like "John*" Then
        c.Font.Name = "Arial Black"
    End If
Next c

다음 코드는 지정된 범위에서 “To Test”를 “Passed”로 바꿉니다:

With Range("a1:a500")
    Set c = .Find("To Test", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            c.Value = "Passed"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With

FindNext를 사용하려면 범위를 지정해야 한다는 점에 유의하세요. 또한 중지 조건을 제공해야 하며, 그렇지 않으면 루프가 영원히 실행됩니다. 일반적으로 처음 찾은 셀의 주소는 변수에 저장되며 해당 셀에 다시 도달하면 루프가 중지됩니다. 또한 루프를 중지하기 위해 아무것도 발견되지 않는 경우도 확인해야 합니다.

Range 주소

A1 스타일로 주소를 가져오려면 Range.Address를 사용합니다.

MsgBox Range("A1:D10").Address
' 또는
Debug.Print Range("A1:D10").Address

xlReferenceStyle(기본값은 xlA1)을 사용하여 R1C1 스타일로 주소를 가져옵니다.

MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)
' 또는
Debug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) 

변수에 저장된 범위를 다루고 특정 주소에 대해서만 처리하려는 경우에 유용합니다.

범위 값을 배열로 변환하기

범위를 배열로 전송한 다음 값을 처리하는 것이 더 빠르고 쉽습니다. 배열의 범위를 채우는 데 필요한 크기를 계산하지 않으려면 배열을 Variant로 선언해야 합니다. 배열의 차원은 범위의 값 수와 일치하도록 설정됩니다.

Dim DirArray As Variant
' 범위의 값을 배열에 저장합니다.

DirArray = Range("a1:a5").Value

' 배열의 값을 읽어서 잘 저장되었는지 확인합니다
For Each c In DirArray
    Debug.Print c
Next

배열 값들을 범위에 입력하기

위 코드블락의 실행이 완료되면 완성된 Array를 Range에 다시 쓸 수 있습니다. 위 예제에서 배열을 Range에 쓰려면 배열과 크기가 일치하는 Range를 지정해야 합니다.

아래 코드를 사용하여 Array를 D1:D5 범위에 씁니다:

Range("D1:D5").Value = DirArray 

Range("D1:H1").Value = Application.Transpose(DirArray)

배열을 한 행에 입력할 경우 반드시 행/열 전환(Transpose)을 해야 한다는 점에 유의하세요.

범위에 대한 Sum 함수 사용하기

SumOfRange = Application.WorksheetFunction.Sum(Range("A1:A10"))
Debug.Print SumOfRange

위 예제 코드에서와 같이 함수 이름 앞에 Application.WorkSheetFunction을 지정하면 Excel에서 사용할 수 있는 많은 함수를 VBA 코드에서 사용할 수 있습니다.

범위에 대한 Count 함수 사용하기

' 범위에 숫자 값이 채워진 셀의 개수를 계산합니다
CountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))
Debug.Print CountOfCells

' Count Number of Non Blank Cells in the Range
CountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))
Debug.Print CountOfNonBlankCells

작성자 : Vinamra Chandra