Range Object - Heeyoung-Ahn/Excel_VBA GitHub Wiki

Range Object

• VBA에서 가장 빈번하게 사용되는 객체
• Range 객체 선택 시 Workbook, Worksheet가 생략되면 현재 활성화된 Workbook, Worksheet 의미 즉, ActiveWorkbook.ActiveWorksheet 생략

---

Range 객체를 선택하는 방법

Range(“A1”).Select

• Cells 속성
  Cells(1, 1), Cells(1, “A”).Select
• 연속된 범위
  Range(“A1”, “C10”).Select, Range(“A1:C10”).Select
Range(Columns(1), Columns(3)).Select, Range("A1:C1").EntireColumn.Select, Columns("A:C").Select
• 연속되지 않은 범위
  Range(“A1, C10”).Select
Range(“A1, C1”).EntireColumn.Select, Union(Columns(“A”), Columns(“C”)).Select
• 선택된 범위에서 첫 번째 셀
  ActiveCell.EntrieRow.Cells(1, 1).Select
• 워크시트 전체
  Cells.Select
• Range.Offset 속성
  Range(“A1”).Offset(1), Range(“A1”).Offset(1, 0), Range(“B3”).Offset(-1, -2)
Set rngS = Range.Find(“찾는문자열”, lookat:= xlwhole)
rngS.Offset(2).Activate
• Offset은 자신이 0, Cells는 자신이 1
• Offset은 병합된 영역은 한 개로 처리, Cells는 병합되어도 셀마다 한 개로 처리

---

Range에 값 입력

Range(“A1”).Value = Date
Range(“A1:B1).Value = Date

---

Range에 수식 입력

Range(“A1”).Formula = “=A2 + A3”
Range(“A1”).FormulaR1C1 = “=SUM(R1C2:R1C3)”

Sub sumFormulaDemo()

    Dim lngStartR As Long
    Dim lngLastR As Long
    
    lngStartR = 2
    lngLastR = Cells(Rows.Count, 1).End(xlUp).Row
    Rnage("A" & lngLastR).Offset(1).Value = "합계'"
    Range("B" & lngLastR).Cells(2, 1).Formula = "=SUM(B2:B" & lngLastR & ")"

End Sub

---

Range 값 지우기

Range(“A1”).Clear

• ClearContents / ClearFormats / ClearOutlines / ClearHyperlinks / ClearNotes
  Range(“A1”).Value = vbNullString

---

Range 복사, 잘라내기, 붙여넣기

Range(“A1:C5”).Copy Destination:=Range(“D1”)
Range(“A1:C5”).Copy
Range(“D1”).PasteSpecial Paste:=xlPasteAll / xlPasteFormulas / xlPasteValues / xlPasteFormats
Range(“D1”).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Range(“A1”).Cut Destination:=Range(“D1”)

---

Range 서식

• Font
  Range(“A1”).Font.Name = “나눔명조”
Range(“A1”).Font.Color = vbRed, Range(“A1”).Font.ColorIndex = 3
Range(“A1”).Font.Size = 14
Range(“A1”).Font.Bold = True
• Interior
  Range(“A1”).Interior.Color = vbYellow
• Borders
  Range(“A1”).Borders.Color = vbBlue
Range(“A1”).Borders.LineStyle = xlContinuous
Range(“A1”).Borders.Weight = xlHairline
• 기타
  Range(“A1”).WrapText = True
Range(“A1”).ShrinkToFit = True
Range(“A1”).NumberFormat = “#,##0”

---

Range 개체의 주요 속성

• Address: Range(“A1”).Address → $A$1`
• Value
• Formula
• NumberFormat(셀서식)
• Column / Columns
• Row / Rows
• CurrentRegion
  cf. Worksheet의 속성 중 UsedRange
• End: xlUp, xlDown, xlToLeft, xlToRight
  Range(“A1”).End(xlUp)
• EntireColumn / EntrieRow
• Resize