【ExcelVBA】Selection プロパティの使い方

Sub selectionSample1()

    Const bookName = "Book1.xlsx"
    Const sheetName = "Sheet1"

    ' アクティブブックの確認
    If ActiveWorkbook.Name <> bookName Then
        Workbooks(bookName).Activate
    End If
    ' アクティブシートの確認
    If ActiveSheet.Name <> sheetName Then
        Sheets(sheetName).Activate
    End If
    ' オブジェクトタイプの確認
    If TypeName(Selection) <> "Range" Then
        MsgBox "セルを選択してください"
    Else
        MsgBox Selection.Address & " が選択されています"
    End If
    
End Sub

Sub objectTypeCheck()

    Cells(1, 1).Value = TypeName(Selection)
End Sub

Sub selectionSample2()
    Dim rng As Range
    
    Set rng = Selection
    MsgBox rng.Address & " が選択されています"
End Sub

Sub selectionSample3()
    MsgBox Selection.Count & "個のセルが選択されています"
End Sub

Sub selectionSample4()
    MsgBox "行数:" & Selection.Rows.Count & vbCrLf _
           & "列数:" & Selection.Columns.Count
End Sub

Sub selectionSample5()
    ' 選択範囲の左上セルに値をセット
    Selection(1).Value = 1000

    MsgBox "選択範囲の左上の値を " & Selection(1).Value & " に変更しました"
End Sub

Sub selectionSample6()
    ' 選択範囲の左上セルに値をセット
    Selection(Selection.Count).Value = 1000

    MsgBox "選択範囲の右下の値を " & Selection(Selection.Count).Value & " に変更しました"
End Sub

Sub selectionSample7()

    ' 1行目の全セルの値変更
    Selection.Rows(1).Value = 10000
    ' 1行目の右端のセルの値変更
    Selection.Rows(1).Cells(Selection.Columns.Count).Value = 3000

    ' 最終行の全セルの値変更
    Selection.Rows(Selection.Rows.Count).Value = 50000
    ' 最終行の右端のセルの値変更
    Selection.Rows(Selection.Rows.Count).Cells(Selection.Columns.Count).Value = 3000    
End Sub

Sub selectionSample8()

    ' 1列目の全セルの値変更
    Selection.Columns(1).Value = 10000
    ' 1列目の2番目のセルの値変更
    Selection.Columns(1).Cells(Selection.Rows.Count).Value = 3000

    ' 最終列の全セルの値変更
    Selection.Columns(Selection.Columns.Count).Value = 50000
    ' 最終列の2番目のセルの値変更
    Selection.Columns(Selection.Columns.Count).Cells(Selection.Rows.Count).Value = 3000
End Sub

Sub selectionSample9()

    Dim result As String
    
    result = "[左上]" & vbCrLf
    result = result & "  列:" & Selection(1).Column & vbCrLf
    result = result & "  行:" & Selection(1).Row & vbCrLf

    result = result & "[右下]" & vbCrLf
    result = result & "  列:" & Selection(Selection.Count).Column & vbCrLf
    result = result & "  行:" & Selection(Selection.Count).Row & vbCrLf

    MsgBox result
    
End Sub

Sub selectionSample10()
    Selection.Copy Range("B2")
End Sub

Sub selectionSample11()
    Range(Cells(2, 2), _
        Cells(2 + Selection.Rows.Count - 1, 2 + Selection.Columns.Count - 1)) _
            .Value = Selection.Value
End Sub