excelvba test if target is precisely a named range code example

Example 1: excelvba test if target is precisely a named range

'VBA inline code to test if Target is a named range:

If Not Intersect(Target, [NamedRangeName]) Is Nothing Then
    If Target.Cells.Count = [NamedRangeName].Cells.Count Then
        If Target.row = NamedRange.row Then
            If Target.Column = NamedRange.Column Then  
                'Place code here to execute when Target is
                'the named range.
            End If
        End If    
    End If
End If



'Or encapsulated into a function:

Function TargetIsNamedRange(Target As Range, NamedRange As Range) As Boolean
    If Not Intersect(Target, NamedRange) Is Nothing Then
        If Target.Cells.Count = NamedRange.Cells.Count Then
            If Target.Row = NamedRange.Row Then
                If Target.Column = NamedRange.Column Then
                    TargetIsNamedRange = True
                    Exit Function
                End If
            End If
        End If
    End If
End Function


'
'
'

Example 2: excelvba test if target is precisely a named range

To get a contiguous range of entire rows, use this VBA function:

Function WSRows(ws As Worksheet, Row1&, Rows&)
    Set WSRows = ws.Rows(Row1).Resize(Rows)
End Function
  
'-----------------------------------------------------------------
  
MsgBox WSRows(Sheet1, 11, 9).Address		'<--displays: $11:$20
  
'To get the values in the range into a variant array:  
v =  WSRows(Sheet1, 11, 9)
   
'-----------------------------------------------------------------
 
  
'Sister function to a get range of contiguous full columns:  
  
Function WSCols(ws As Worksheet, Col1&, Cols&)
    Set WSCols = ws.Columns(Col1).Resize(, Cols)
End Function