[VBA] Range와 Cell 완벽 가이드

이 포스트는 Excel Macro Mastery 사이트의 'The Complete Guide to Range and Cells in Excel VBA(by Paul Kelly)'의 내용을 다시 정리한 것입니다. 이번 포스트에서는 엑셀 VBA의 Range와 Cell에 대해 다룹니다.

Range & Cell 사용법 요약

함수	파라메터	리턴 타입	예	결과
Range	cell 주소	멀티 셀	Range("A1:A4")	$A$1:$A$4
Cells	행(row) 번호, 열(column) 번호	단일 셀	Cells(1, 5)	$E$1
Offset	행(row) 번호, 열(column) 번호	멀티 셀	Range("A1:A2").Offset(1, 2)	$C$2:$C$3
Rows	행(row) 번호 또는 행(row) 범위	단일 또는 멀티 셀	Rows(4) Rows("2:4")	$4:$4 $2:$4
Columns	열(column) 번호 또는 열(column) 범위	단일 또는 멀티 셀	Columns(4) Columns("B:D")	$D:$D $B:$D

소개

본 포스트는 엑셀 VBA의 3대 요소 (Workbooks, Worksheets, Range와 Cell) 중 우리가 가장 자주 쓰게 될 'Range 와 Cell'에 대해 다루고 있습니다.

엑셀의 워크북을 하나의 책 또는 서류철, 워크시트를 그 안의 종이 한장이라고 표현하면, 셀은 종이 한장 위의 여러 네모칸을 의미 합니다. 이 셀이라는 단위에 우리는 보통 아래와 같은 작업들을 합니다.

• 셀의 값을 읽는다.
• 셀에 값을 쓴다.
• 셀의 포멧을 변경한다.

엑셀에서는 셀에 접근하고 위의 작업들을 하기 위해 Range, Cells, Offset과 같은 방법들을 제공합니다. 가장 먼저 워크시트의 Range 프로퍼티를 시작으로 다른 방법들을 살펴 보도록 하겠습니다.

Range 프로퍼티

워크시트는 VBA에서 셀들에 접근할 수 있도록 하는 Range 프로퍼티를 가지고 있습니다. Range의 인자는 "A1", "A3:C6" 처럼 엑셀 워크시트에서 사용하는 것과 똑같은 인자를 사용합니다. 아래 코드 예제는 Range를 이용해 엑셀 시트에 값을 어떻게 셋팅하는지 보여 줍니다

' 현재 워크북 Sheet1 워크시트의 A1 셀에 숫자 67을 적는다
ThisWorkbook.Worksheets("Sheet1").Range("A1").Value2 = 67

' 현재 워크북 Sheet1 워크시트의 A2 셀에 문자열을 적는다
ThisWorkbook.Worksheets("Sheet1").Range("A2").Value2 = "John Smith"

' 현재 워크북 Sheet1 워크시트의 A3 셀에 날짜를 적는다.
ThisWorkbook.Worksheets("Sheet1").Range("A3").Value2 = #11/21/2017#

또한 여러 셀을에 한번에 접근하는 것도 가능합니다. 역시 엑셀 시트에서 사용하는 것과 동일한 인자를 사용합니다.

' Write number to a range of cells
Sheet1.Range("A1:A10").Value2 = 67

' Write text to multiple ranges of cells
Sheet1.Range("B2:B5,B7:B9").Value2 = "John Smith"

Value2 ??

Value면 Value지 Value2는 뭔지, Value와 Value2의 차이는 뭔지 궁금하신 분들이 많을 것입니다.
MSDN의 Range.Value2 문서에 따르면 Value와 Value2 프로퍼티의 유일한 차이는 Value2는 Currency와 Date 데이터 타입을 처리하지 않는다는 것입니다. Value2는 Currency 또는 Date 타입으로 포멧된 셀 값을 Double형 데이터 타입으로 처리합니다. 이해를 돕기 위해 아래 예제를 살펴 보면, 각각 Currency과 Date 타입으로 셀서식이 지정된 셀의 값을 VBA 코드에서 Value와 Value2을 이용해 출력하고 있습니다.

B1:Currency, B2:Date

Sub WriteToCell()
    Dim vCell_value As Variant
    Dim vCell_value2 As Variant
    
    vCell_value = ThisWorkbook.Sheets("Sheet1").Range("B1").Value
    vCell_value2 = ThisWorkbook.Sheets("Sheet1").Range("B1").Value2
    Debug.Print vCell_value
    Debug.Print vCell_value2
    
    vCell_value = ThisWorkbook.Sheets("Sheet1").Range("B2").Value
    vCell_value2 = ThisWorkbook.Sheets("Sheet1").Range("B2").Value2
    Debug.Print vCell_value
    Debug.Print vCell_value2
End Sub

아래 지역 조사 창에서 vCell_value2는 Variant/Double 형태로 저장되어 있음을 확인 할 수 있습니다.

보통의 경우는 Value2에 대해 알필요도 없고 알기도 힘들지만, Date와 Currency 타입을 자주 재계산하는 과학 프로젝트 또는 회계관련 작업에서는 Date 또는 Currency를 처리하기 위한 Variant 타입 보다 Double 타입을 사용하는 것이 더 빠르기 때문이라고 합니다.

Cell 프로퍼티

워크시트 개체는 Cells라는 Range와 매우 비슷하지만 다른 프로퍼티도 가지고 있습니다. Range와 Cells는 다음과 같이 두가지 큰 차이점이 있습니다

• Cells는 딱 하나의 셀을 가진 Range개체를 리턴한다.
• Cells는 행번호, 열번호를 인자로 받는다(Range는 "A1"과 같이 받았다)

아래 예제는 Cells와 Range 프로퍼티를 비교해가며 같은 셀에 어떻게 다른 방법으로 접근 할 수 있는지를 보여 줍니다

' Write to A1
Sheet1.Range("A1").Value2 = 10
Sheet1.Cells(1, 1).Value2  = 10

' Write to A10
Sheet1.Range("A10").Value2 = 10
Sheet1.Cells(10, 1).Value2  = 10

' Write to E1
Sheet1.Range("E1").Value2 = 10
Sheet1.Cells(1, 5).Value2  = 10

여러분은 아마도 어떤 경우에 Range를 사용해야하고 Cells를 사용해야 할지 의문이 드실 수 있습니다. 아래 예제는 어떤 경우에 Cells를 사용하면 편할지 보여줍니다.

For i = 1 To 10
    ThisWorkbook.Sheets("Sheet1").Cells(1, i).Value = 100 * i
Next

위 예제에서 컬럼을 이동하기 위해 A, B, C...와 같이 사용하는 대신 컬럼 넘버를 이용했습니다. 참고로 Cells의 인자는 열번호, 행번호 순서입니다. 아무래도 알파벳을 증가하는 것 보다 숫자를 사용하는 편이 For문을 이용하기 훨씬 편합니다.

Cells와 Range를 함께 사용하기

하나의 셀에 접근하기 위해서 Cells를 사용했습니다. 만일 여러분이 여러 셀을 동시에 접근하고 싶으시다면 아래 처럼 Cells와 Range를 동시에 사용 하실 수도 있습니다.

With Sheet1
    ' Cells 프로퍼티를 이용해 A1부터 A10까지 5를 적습니다.
    .Range(.Cells(1, 1), .Cells(10, 1)).Value = 5
    ' B1 부터 Z1 까지 bold를 적용합니다
    .Range(.Cells(1, 2), .Cells(1, 26)).Font.Bold = True
End With

Range의 시작 셀과 마지막 셀을 Cells를 이용해 지정 할 수 있습니다. 때때로 A1:B2와 같은 주소형식으로 범위를 다루는 것 보다 번호를 이용하는 것이 훨씬 편할 때가 있습니다. 또한 Range는 Address라는 프로퍼티를 가지고 있는데, 이는 숫자로 셀을 지정했다고 하더라도 "A1"과 같은 포멧으로 리턴 합니다.

With Sheet1

        Debug.Print .Range(.Cells(1, 1), .Cells(10, 1)).Address
        Debug.Print .Range(.Cells(1, 2), .Cells(1, 26)).Address

End With

주소를 숫자로 지정했지만 결과는 아래와 같이 문자/숫자 형식으로 리턴 됩니다.

$A$1:$A$10
$B$1:$Z$1

Offset 프로퍼티

Range는 Offset이라는 프로퍼티를 가지고 있습니다. 오프셋이란 원래 위치에서 얼마나 떨어져 있는가를 나타냅니다. 여러 분은 오프셋을 통해 현재 위치로 부터 일정거리 만큼 떨어진 곳으로 부터 현재 Range와 동일한 사이즈의 Range를 얻을 수 있습니다. 예를 들어 .Range("A1:A3").Offset(1,1)은 오른쪽으로 +1, 아래로 +1 이동한 동일한 사이즈의 Range, 즉, B2:B4를 리턴합니다. 아래는 Offset을 사용하는 다양한 예입니다.

' B2에 쓰기. 아무런 offset 없음. 예를 보여주기 위해 쓴거지 실제로 이렇게 코드 안 씀
Sheet1.Range("B2").Offset().Value2 = "Cell B2"

' B2에서 컬럼이 우측으로 1증가한 C2에 쓰기
' (0이라서 row를 생략 했는데 0이라도 명시적으로 적어주는 것이 코드 가독성에 도움 됨)
Sheet1.Range("B2").Offset(, 1).Value2 = "Cell C2"

' B2에서 로우가 아래로 1증가한 B3에 쓰기
Sheet1.Range("B2").Offset(1).Value2 = "Cell B3"

' B2에서 컬럼과 로우가 각각 1씩 증가한 C3에 쓰기
Sheet1.Range("B2").Offset(1, 1).Value2 = "Cell C3"

' B2에서 컬럼과 로우가 각각 1씩 감소한 A1에 쓰기
Sheet1.Range("B2").Offset(-1, -1).Value2 = "Cell A1"

' "D2:F12" 범위에서 컬럼과 로우가 1씩 증가한 "E3:G13" 범위에 쓰기
Sheet1.Range("D2:F12").Offset(1, 1).Value2 = "Cells E3:G13"

Range의 CurrentRegion 사용하기

CurrentRegion은 주어진 Range에 인접한 값을 가진 모든 인접한 셀들의 Range를 리턴합니다. 한글로는 이해가 쉽지 않으니 그림으로 이해를 돕도록 하겠습니다.

CurrentRegion

위의 예에서 값을 가지고 있는 셀은 B2:D5입니다.

• Range("B3").CurrentRegion 은 B2:D5 범위를 리턴합니다.
• Range("D5").CurrentRegion 도 B2:D5 범위를 리턴합니다.
• Range("C4:D5").CurrentRegion 역시 B2:D5 범위를 리턴합니다.

만일 값이 비어 있더라도 해당 컬럼이나 로우에 값이 있는 셀이 하나라도 있다면 그 로우나 컬럼까지도 범위에 포함됩니다.

Rows와 Columns

만일 전체 행(row) 또는 전체 열(column)에 대해 무잇인가를 해야 할 필요가 있을 때는 워크시트의 Rows와 Columns 프로퍼티를 사용 할 수 있습니다. 두 프로퍼티 다 접근하려는 행번호 또는 열번호 하나만을 인자로 필요로 합니다.

' B컬럼의 모든 셀들의 폰트 사이즈를 9로 만든다
Sheet1.Columns(2).Font.Size = 9

' 컬럼 D에서 F까지의 모든 셀들의 너비를 4로 만든다
Sheet1.Columns("D:F").ColumnWidth = 4

' 로우 5의 모든 셀들의 폰트 사이즈를 18로 만든다
Sheet1.Rows(5).Font.Size = 18

워크시트의 Rows, Columns 대신에 Range의 Rows, Columns 프로퍼티를 사용 할 수도 있습니다. 워크시트에서 Columns(1)을 지정하면 컬럼 'A' 가리켰습니다만, .Range("B1:D10").Columns(1) 은 컬럼 'B'를 가리킵니다.
Columns과 Row의 인자는 해당 범위에서 몇번째 컬럼인지 또는 로우인지를 지정합니다. 다만 워크시트일 경우에는 시트 전체가 범위에 포함되어 가장 첫번째 열인 1열이 'A'열을 가리켰던 겁니다.

셀 복사

간단하게 '대상 셀 = 원본 셀' 형식으로 만들어 주시면 됩니다.

'B2 셀의 값을 A1에 복사
Sheet1.Range("A1").Value = Sheet1.Range("B1").Value

'Sheet2의 A1 셀의 값을 Sheet1의 A1셀로 복사
Sheet1.Range("A1").Value = Sheet2.Range("A1").Value

'B1 셀의 값을 A1:A5 범위 셀에 복사
Sheet1.Range("A1:A5").Value = Sheet1.Range("B1").Value

'B2:D5 범위의 값을 E2:G5범위로 복사
Sheet1.Range("E2:G5").Value = Sheet1.Range("B2:D5").Value

위 예제에서는 1:1 복사 밖에 되지 않았습니다. 하지만 Range의 Copy 프로퍼티를 사용하면 하나의 셀을 동시에 여러 셀로 복사 할 수 있습니다. 또한 Copy 함수는 값 뿐만 아니라 셀의 서식(포멧)도 같이 복사 합니다.

Dim rgCopy As Range
Set rgCopy = Sheet1.Range("B1:B5")

' B1:B5 셀들을 A1:A5와 C2:C6 범위에 동시에 복사
rgCopy.Copy Destination:=Sheet1.Range("A1:A5,C2:C6")

Range.Resize 메소드 사용하기

특정 범위를 '=' 연산자를 사용하여 복사 할 때 대상 범위는 원본과 동일한 사이즈여야 합니다. 만일 복사 대상 범위가 원본 보다  작으면 복사 되는 셀들이 작은 셀에 맞춰 잘려서 복사 되고, 복사 대상 범위가 원본 보다 크면 '1004 런타임 오류가 발생하였습니다: 응용 프로그램 정의 오류 또는 개체 정의  오류입니다' 오류가 발생합니다.

그래서 필요하다면 Resize 메소드를 이용해 복사 대상 범위 사이즈를 변경 해줄 수 있습니다.

' A1 셀 선택
Sheet1.Range("A1").Select

' A1 셀로 부터 너비가 세로 2, 가로 1인 A1:A2 셀 선택
Sheet1.Range("A1").Resize(2, 1).Select

' A1 셀로 부터 너비가 세로 5, 가로 1인 A1:A5 셀 선택
Sheet1.Range("A1").Resize(5, 1).Select
    
' A1 셀로 부터 너비가 세로 1, 가로 4인 A1:A5 셀 선택
Sheet1.Range("A1").Resize(1, 4).Select
    
' A1 셀로 부터 너비가 세로 3, 가로 3인 A1:C3 셀 선택
Sheet1.Range("A1").Resize(3, 3).Select

복사 대상 범위의 사이즈를 조절하고 싶다면 아래와 같이 원본의 범위 사이즈를 이용 할 수 있습니다.

Dim rgSrc As Range, rgDest As Range
    
' CurrentRegion 프로퍼티를 이용해 "A1" 셀에 인접한 Range를 얻어 옵니다.
Set rgSrc = Sheet1.Range("A1").CurrentRegion

' 복사 대상 워크시트의 Range를 얻어 옵니다.
Set rgDest = Sheet2.Range("A1")

' 원본 Range의 Rows.Count와 Columns.Count를 이용해 복사 대상 Range의 사이즈를 조절
Set rgDest = rgDest.Resize(rgSrc.Rows.Count, rgSrc.Columns.Count)
    
rgDest.Value2 = rgSrc.Value2

With을 이용해 코드를 더 간단히 만들 수도 있습니다.

Dim rgSrc As Range
    
' CurrentRegion 프로퍼티를 이용해 "A1" 셀에 인접한 Range를 얻어 옵니다.
Set rgSrc = Sheet1.Range("A1").CurrentRegion
    
With rgSrc
    Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

Cell 값을 변수에 복사하기

지금까지 셀의 값을 다른 셀에 복사하는 방법을 알아 보았습니다. 이번에는 셀에서 값을 읽어 변수에 저장하는 방법에 대해 알아 보도록 하겠습니다. 변수에 대한 자세한 설명은 [여기]를 참조해 주세요.

' 정수 타입 변수 'number' 선언
Dim number As Long

' 숫자를 "A1" 셀로 부터 읽어 number 변수에 저장
number = Sheet1.Range("A1").Value

' number 변수에 1을 더하고 다시 number에 저장
number = number + 1

' 저장된 number 변수를 "A2" 셀에 저장
Sheet1.Range("A2").Value = number

' 문자열 타입 변수 'text' 선언
Dim text As String

text = Sheet1.Range("A1").Value

Sheet1.Range("A2").Value = text

하나의 셀 뿐만 아니라 Range에도 변수 값을 지정할 수 있습니다. 이 경우에는 범위에 속한 모든 셀들의 값이 동일하게 변경 됩니다.

' "A1:B10" 범위에 66이라고 쓴다
Sheet1.Range("A1:B10").Value = 66

여러 셀들의 값을 하나의 변수에 담을 수는 없습니다. 하지만 배열에는 가능합니다. 아래에서 여러 셀들의 값을 배열 변수에 저장하는 방법에 대해 알아 보도록 하겠습니다.

Cell의 서식까지 복사하기

앞에서는 셀의 '값'을 복사하는 방법에 대해 알아보았습니다. 이번 장에서는 Range의 Copy 프로퍼티를 이용하여 셀의 서식까지 함께 복사 할 수 있는 방법에 대해 알아 보도록하겠습니다.

Range("A1:B4").Copy Destination:=Range("C5")

Copy 프로퍼티를 이용하면 값 뿐만 아니라 아래 처럼 서식까지 모두 복사가 가능 합니다.  

Copy를 이용하면 우리가 엑셀에서 ctrl + c를 한것과 같이 클립 보드에 복사해줍니다. 만일 개별 속성들을 구분해서 복사 하고 싶다면 아래 처럼 PasteSpecial 프로퍼티를 이용하여 개별 복사를 원하는 속성들을 지정 해줄 수 있습니다.  

Range("A1:B4").Copy
'셀의 값 복사. 텍스트 bold, under line, color 들도 같이 복사 됩니다.
Range("C5").PasteSpecial Paste:=xlPasteValues
'셀의 서식 복사. 셀 채우기, 테두리 등이 복사 됩니다. 
Range("C5").PasteSpecial Paste:=xlPasteFormats

복사 타입에 대한 보다 자세한 사항은 [여기]를 확인 해주세요.

Range 값들을 배열에 저장하기

' 배열을 생성한다
Dim StudentMarks() As Variant

' 첫번째 열의 26개 값을 배열에 저장
StudentMarks = Range("A1:Z1").Value

' Do something with array here

' 저장된 배열을 3번째 행에 쓰기
Range("A3:Z3").Value = StudentMarks

NOTE - Range.Value로 부터 값을 읽어 들인 배열은 2차원 배열이 됩니다. 이유는 스프레드 시트가 행과 열. 이렇게 2차원 배열로 값을 저장하기 때문입니다.

특정 범위 안의 셀들 순회하기

아래는 "A1:A10,A20" 범위의 셀들을 모두 순회하며 음수값을 가지고 있는 셀들의 주소를 프린트 하는 예제 입니다.

Dim rg As Range
For Each rg In Sheet1.Range("A1:A10,A20")
    ' Print address of cells that are negative
    If rg.Value < 0 Then
        Debug.Print rg.Address + " is negative."
    End If
Next

위와 같은 예제를 For 구문으로 구현 할 수도 있습니다. 인덱스를 직접 지정 할 수 있는 For문이 사용하기 더 유연하지만,  For Each 보다 다소 느립니다.

' Go through cells from A1 to A10
Dim i As Long
For i = 1 To 10
  ' Print address of cells that are negative
  If Range("A" & i).Value < 0 Then
      Debug.Print Range("A" & i).Address + " is negative."
  End If
Next
 
' Go through cells in reverse i.e. from A10 to A1
For i = 10 To 1 Step -1
    ' Print address of cells that are negative
    If Range("A" & i) < 0 Then
        Debug.Print Range("A" & i).Address + " is negative."
    End If
Next

셀 포멧팅

엑셀에서 셀의 포멧을 결정하는게 편하긴 하지만 때때로 VBA 코드상에서 동적으로 포멧을 결정해야 하는 경우도 종종 있습니다. 아래는 셀의 포멧을 지정하기 위한 VBA 예제입니다.

Public Sub FormattingCells()
    With Sheet1
    
        ' Format the font
        .Range("A1").Font.Bold = True
        .Range("A1").Font.Underline = True
        .Range("A1").Font.Color = rgbNavy
        
        ' Set the number format to 2 decimal places
        .Range("B2").NumberFormat = "0.00"
        ' Set the number format to a date
        .Range("C2").NumberFormat = "dd/mm/yyyy"
        
        ' Set the number format to general
        .Range("C3").NumberFormat = "General"
        
        ' Set the number format to text
        .Range("C4").NumberFormat = "Text"
        
        ' Set the fill color of the cell
        .Range("B3").Interior.Color = rgbSandyBrown
        
        ' Format the borders
        .Range("B4").Borders.LineStyle = xlDash
        .Range("B4").Borders.Color = rgbBlueViolet
        
    End With
End Sub

요약

• Range는 지정된 범위 내의 셀들을 리턴한다.
• Cell은 지정된 셀 하나만 리턴한다
• 셀 간 복사가 가능하다.
• Range 간 복사가 가능하다
• 셀과 변수 간 복사가 가능하다
• 범위 내에 있는 여러개의 셀들과 배열 변수간 복사가 가능하다
• 범위 내의 모든 셀들을 순회하기 위해 For 또는 For Each 구문을 사용하면 된다.

부록 1. 같이 보면 좋은 글

• [Excel] VBA - Workbook 완벽 가이드
• [Excel] VBA - Worksheet 완벽 가이드
• [Excel] VBA - 배열 완벽 가이드
• [Excel] VBA - 고급 필터 완벽 가이드
• [Excel] Visual Basic for Application(VBA)
• [Excel] VBA - 객체(Object) 완벽 가이드