月別: 2020年1月

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

Set myRng2 = Nothing
End Sub

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

Option Explicit
Sub A_Sample018()
Dim myRng As Range
Set myRng = Rows(1).Find(What:=”*”, After:=Range(“A1”) _
, LookIn:=xlFormulas, SearchDirection:=xlPrevious)
If myRng Is Nothing Then
MsgBox “何も入力されていません。”
Else
MsgBox myRng.Address
End If
End Sub

Option Explicit
Sub A_Sample017()
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Cells(1, Columns.Count)
With myRng1
If Len(.PrefixCharacter & .Formula) > 0 Then
Set myRng2 = myRng1    ‘右端のセルが該当する場合
Else
With .End(xlToLeft)
If Len(.PrefixCharacter & .Formula) > 0 Then
Set myRng2 = .Cells(1)
End If
End With
End If
End With
If myRng2 Is Nothing Then
MsgBox “何も入力されていません。”
Else
MsgBox myRng2.Address
End If
Set myRng1 = Nothing    ‘オブジェクトの解放
Set myRng2 = Nothing
End Sub

 

Option Explicit
Sub A_Sample016()
‘Range(“A1:C1”)が結合されているものとします
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Range(“B1”)    ‘任意のセル範囲
‘準備ここまで
If myRng1.MergeCells Then
Set myRng2 = myRng1.Cells(1).MergeArea
MsgBox myRng2.Address
Else
MsgBox “指定したセル範囲は、結合セルの一部ではありません”
End If
End Sub

Option Explicit
Sub A_Sample015()
Dim myRng     As Range
Dim myNameStr As String
Dim myName    As Name
myNameStr = “myArea”                        ‘任意の名前
‘準備ここまで
On Error Resume Next
Set myName = Application.Names(myNameStr)
On Error GoTo 0
If myName Is Nothing Then
MsgBox “定義されている名前はありません”
GoTo ErrHdl
Else
On Error Resume Next
Set myRng = myName.RefersToRange
‘Set myRng = Range(myNameStr)　　       ‘Rangeプロパティ使用
‘Set myRng = [myArea]　　　　　　       ‘範囲名直接指定
On Error GoTo 0
If myRng Is Nothing Then
MsgBox “定義されている名前はセル以外を参照しています”
GoTo ErrHdl
Else
MsgBox myRng.Address
End If
End If
Exit Sub
ErrHdl:
Set myRng = Nothing                 ‘オブジェクトの解放
Set myName = Nothing
End Sub

Option Explicit
Sub A_Sample014()
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Range(“A1:C30”)                ‘任意のセル範囲
‘準備ここまで
On Error Resume Next
Set myRng2 = myRng1.SpecialCells _
(Type:=xlCellTypeConstants, Value:=xlTextValues)
On Error GoTo 0
If myRng2 Is Nothing Then
MsgBox “対象セルはありません”
Else
MsgBox myRng2.Address
End If
Set myRng1 = Nothing                        ‘オブジェクトの解放
Set myRng2 = Nothing
End Sub

Option Explicit
Sub A_Sample012()
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Range(“A1:C30”)        ‘任意のセル範囲
‘準備ここまで
On Error Resume Next
Set myRng2 = myRng1.SpecialCells _
(Type:=xlCellTypeConstants)
On Error GoTo 0
If myRng2 Is Nothing Then
MsgBox “対象セルはありません”
Else
MsgBox myRng2.Address
End If
Set myRng1 = Nothing                ‘オブジェクトの解放
Set myRng2 = Nothing
End Sub

 

Option Explicit
Sub A_Sample011()
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Range(“A1:C30”)                        ‘任意のセル範囲
‘準備ここまで
On Error Resume Next
Set myRng2 = myRng1.SpecialCells _
(Type:=xlCellTypeFormulas, Value:=xlTextValues)
On Error GoTo 0
If myRng2 Is Nothing Then
MsgBox “対象セルはありません”
Else
MsgBox myRng2.Address
End If
Set myRng1 = Nothing                                ‘オブジェクトの解放
Set myRng2 = Nothing
End Sub

 

Option Explicit
Sub A_Sample010()
Dim myRng1 As Range
Dim myRng2 As Range
Dim mySht As Worksheet
Set mySht = Worksheets(1)                           ‘任意のシート
Set myRng1 = mySht.UsedRange                        ‘使用セル範囲
‘準備ここまで
Set myRng2 = myRng1.SpecialCells(xlCellTypeLastCell)
MsgBox myRng2.Address
Set myRng1 = Nothing                                ‘オブジェクトの解放
Set myRng2 = Nothing
Set mySht = Nothing
End Sub