プログラミングのメモ

プログラミングの学び直し備忘録

VBA:Excel :Range

Range設定

Sub Range設定()

    '======================================
    '// セル、範囲
    '======================================
    '// 単一セル
    Range("G2").Select
    Cells(3, 2).Select
    Cells(4, "B").Select
    
    '// 範囲
    Range("G2", "I2").Select '// G2~I2
    Range("G2:I2").Select '// G2~I2
    Range(Cells(2, 7), Cells(2, 9)).Select
    
    '// 離れた
    Range("G2, I2").Select '// G2,I2
    
    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
    
    '======================================
    '// 行、列
    '======================================
    '====================================
    '[Rows / Columns]
    ' 単一行:Rows(2)
    ' 単一列:Columns("G")
    ' 単一列:Columns(2)
    ' 連続列:Columns("F:E")
    '-------------------------------------
    '[Range]
    ' 単一行:Range("8:8").Select
    ' 離れた行:Range("2:2,4:4,6:8").Select

    ' 連続列:Range("E:F").Select
    ' 離れた列:Range("E:E,G:H").Select

    ' 指定Rangeを含む行:Range("E1,G1:H1").EntireRow.Select
    ' 指定Rangeを含む行:Range("E1,G1:H1").EntireColumn.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
    
    '// Row
    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  '// 行全体(Entire:全体)
    rng.Rows(rng.Rows.Count).EntireRow.Select
    
    '// Col
    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  '// 列全体(Entire:全体)
    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
    
    '======================
    '// Row
    '======================
    Debug.Print rng.Rows(1).Address             '// レンジ内 行アドレス
    Debug.Print rng.Rows(1).Row                 '// 行番号
    Debug.Print rng.Rows(1).EntireRow.Address   '// 行全体(Entire:全体)
    rng.Rows(1).EntireRow.Select
    
    '======================
    '// Col
    '======================
    Debug.Print rng.Columns(1).Address               '// レンジ内 列アドレス
    Debug.Print rng.Columns(1).Column                '// 列番号
    Debug.Print rng.Columns(1).EntireColumn.Address  '// 列全体(Entire:全体)
    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