Range設定
Sub Range設定()
Range("G2").Select
Cells(3, 2).Select
Cells(4, "B").Select
Range("G2", "I2").Select
Range("G2:I2").Select
Range(Cells(2, 7), Cells(2, 9)).Select
Range("G2, I2").Select
Dim rng As Range
Set rng = Cells(2, 7)
Set rng = Union(rng, Cells(2, 9))
Set rng = Union(rng, Cells(4, 9))
rng.Select
Range("E:F").EntireColumn.Select
Range("E:E,G:H").Select
Range("E1,G1:H1").EntireColumn.Select
Columns("G").Select
Columns(5).Select
Set rng = Columns(5)
Set rng = Union(rng, Columns(7), Columns(10))
rng.Select
End Sub
範囲取得
Sub 範囲取得()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("TEST")
ws.Range("B2").CurrentRegion.Select
Set ws = Nothing
End Sub
最終行/列
Sub 最終行_列()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("TEST")
Dim rng As Range
Set rng = ws.Range("B2").CurrentRegion
Debug.Print rng.Rows(rng.Rows.Count).Address
Debug.Print rng.Rows(rng.Rows.Count).Row
Debug.Print rng.Rows(rng.Rows.Count).EntireRow.Address
rng.Rows(rng.Rows.Count).EntireRow.Select
Debug.Print rng.Columns(rng.Columns.Count).Address
Debug.Print rng.Columns(rng.Columns.Count).Column
Debug.Print rng.Columns(rng.Columns.Count).EntireColumn.Address
rng.Columns(rng.Columns.Count).EntireColumn.Select
Set ws = Nothing
End Sub
開始行/列
Sub 先頭行_列()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("TEST")
Dim rng As Range
Set rng = ws.Range("B2").CurrentRegion
Debug.Print rng.Rows(1).Address
Debug.Print rng.Rows(1).Row
Debug.Print rng.Rows(1).EntireRow.Address
rng.Rows(1).EntireRow.Select
Debug.Print rng.Columns(1).Address
Debug.Print rng.Columns(1).Column
Debug.Print rng.Columns(1).EntireColumn.Address
rng.Columns(1).EntireColumn.Select
Set ws = Nothing
End Sub
セル(行頭先頭/行頭最後/行末先頭/行末最後
Sub セル()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("TEST")
Dim rng As Range
Set rng = ws.Range("B2").CurrentRegion
Dim rng_Cell As Range
Set rng_Cell = rng(1, 1)
rng_Cell.Select
Debug.Print rng_Cell.Address
Set rng_Cell = rng(1, rng.Columns.Count)
rng_Cell.Select
Debug.Print rng_Cell.Address
Set rng_Cell = rng(rng.Rows.Count, 1)
rng_Cell.Select
Debug.Print rng_Cell.Address
Set rng_Cell = rng(rng.Rows.Count, rng.Columns.Count)
rng_Cell.Select
Debug.Print rng_Cell.Address
Set rng = Nothing
Set ws = Nothing
End Sub