Return nameof Left column in current view

macropod · #2 09-19-2018, 06:53 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Picture attached. Need one column to return sum of another column.	IRHSAM	Excel	3	11-18-2017 01:21 AM
Cursor Will Not Return to Left Margin	danpbphoto	Word	1	06-25-2015 07:43 PM
Return Sum value of one column from cells not blank in another column	zulugandalf	Excel	3	08-14-2014 03:37 AM
Need to search a column for a macth and return a result from a third column	pdfaust	Excel	2	02-03-2011 03:02 PM
How to return to where you left off? Shift+F5 not working.	WaltR	Word	2	12-24-2010 11:33 AM