View Single Post
 
Old 09-19-2018, 06:53 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

It's not clear from your post whether AD01 may be in a named range, or if you want just the column's address. For the column, all you need is:

ActiveCell.EntireColumn.Address

If you're referring to a range name that might apply to a cell, that's a bit more complicated, since a Range object can have more than one name and there’s no Names collection for the Range object. The following macro shows how you can use Name with a Range object to return the names assigned to the range:
Code:
Sub FindRangeName()
Dim oName As Name, oRng As Range, oCell As Range
On Error Resume Next
For Each oName In ActiveWorkbook.Names
  Set oRng = Range(oName.Name)
  If Err = 0 Then
    If Not Intersect(ActiveCell, oRng) Is Nothing Then
      If oRng.Address = ActiveCell.EntireColumn.Address Then _
      MsgBox "The Name " & oName.Name & " spans the selected column."
    End If
    Err = 0
  End If
Next
End Sub
As coded, the name must span the entire column; no more and no less.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote