VBA/Excel/Access/Word/Excel/Range Add

To add a range name based on a selection using a variable

 
Sub AddName3()
    Dim rngSelect As String
    rngSelect = Selection.Address
    ActiveSheet.Names.Add Name:="MyRange3", RefersTo:="=" & rngSelect
End Sub

To add a range name for known range

 
Sub AddName1()
   ActiveSheet.Names.Add Name:="MyRange1", RefersTo:="=$A$1:$B$10"
End Sub

Use ActiveCell and ActiveCell.End to create Range

 
Sub SelectDown()
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub