Range Object
Neo Ahn edited this page Feb 11, 2020
·
1 revision
- VBA에서 가장 빈번하게 사용되는 객체
- Range 객체 선택 시 Workbook, Worksheet가 생략되면 현재 활성화된 Workbook, Worksheet 의미 즉, ActiveWorkbook.ActiveWorksheet 생략
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(“A1”).Value = Date
Range(“A1:B1).Value = Date
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(“A1”).Clear
- ClearContents / ClearFormats / ClearOutlines / ClearHyperlinks / ClearNotes
Range(“A1”).Value = vbNullString
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”)
-
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”
- 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