月別: 2019年3月

Option Explicit
Sub A_Sample006()
Dim myRng1 As Range
Dim myRng2 As Range
Dim myLngR As Long
Dim myLngC As Long
myLngR = 5                                  ‘行移動
myLngC = 3                                  ‘列移動
Set myRng1 = Range(“A1:C5”)                 ‘任意のセル範囲
‘準備ここまで
Set myRng2 = myRng1.Offset(RowOffset:=myLngR, ColumnOffset:=myLngC)
MsgBox myRng2.Address
Set myRng1 = Nothing                        ‘オブジェクトの解放
Set myRng2 = Nothing
End Sub

Option Explicit
Sub A_Sample005()
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Range(“A1”)                    ‘任意のセル
‘準備ここまで
Set myRng2 = myRng1.CurrentRegion
MsgBox myRng2.Address
Set myRng1 = Nothing                        ‘オブジェクトの解放
Set myRng2 = Nothing
End Sub

‘Module1
Option Explicit
Sub A_Sample004()
Dim myRng As Range
Dim mySht As Worksheet
Set mySht = Worksheets(1)   ‘任意のシート
‘準備ここまで
Set myRng = mySht.UsedRange
MsgBox myRng.Address
Set myRng = Nothing         ‘オブジェクトの解放
Set mySht = Nothing
End Sub

‘Module1
Option Explicit
Sub A_Sample003()
Dim myRng As Range
Dim myStr As String
myStr = “マウスでセル範囲を選択して、OKボタンを押してください”
On Error Resume Next
Set myRng = Application.InputBox(myStr, Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox “キャンセルされました”
Else
MsgBox myRng.Address
End If
Set myRng = Nothing                         ‘オブジェクトの解放
End Sub

Sub A_Sample002()
Dim myRng As Range
On Error Resume Next
Set myRng = ActiveCell
On Error GoTo 0
If myRng Is Nothing Then
MsgBox “ActiveCellが取得できません”
Else
MsgBox myRng.Address
End If
Set myRng = Nothing                         ‘オブジェクトの解放
End Sub

 

Sub A_Sample001()
Dim myRng As Range
If TypeName(Selection) = “Range” Then
Set myRng = Selection
MsgBox myRng.Address
Else
MsgBox “セルが選択されていません”
End If
Set myRng = Nothing    ‘オブジェクトの解放
End Sub