#1
|
|||
|
|||
Return nameof Left column in current view
Back Again, I am trying to get the name of the column that is the current left most column in view, not the left most column as in A, but if I scroll to say AD01 can VBA return the name of that column, not the current cell name. |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks for the reply, that's close to what I need, but I already know the active cell as I have just clicked on it. What I need to know is after doing this what is the column to the left of the screen.
I could scroll some place in the W/S and then click on a random cell say "AD10", that's not in the left most visible column, but I need to know what the left most column is. It could be AA1, BB1... |
#4
|
||||
|
||||
In that case, try:
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 With ActiveWindow.ActivePane.VisibleRange.Cells(1, 1) If Not Intersect(.Address, oRng) Is Nothing Then If oRng.Address = .EntireColumn.Address Then _ MsgBox "The Name " & oName.Name & " spans " & .EntireColumn.Address End If End With Err = 0 End If Next End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks again,
But after copying your code as is nothing happened, I remove the on error line and it fails on... Code:
For Each oName In ActiveWorkbook.Names Set oRng = Range(oName.Name) hovering over oname shows it as oName.Name = "_xlfn.COUNTIFS" oRng = nothing perhaps oName is not pickup any names in the Activewookbook.Names |
#6
|
|||
|
|||
this of any use ?
Code:
Sub Testing_1() MsgBox ActiveWindow.VisibleRange.Address End Sub |
#7
|
|||
|
|||
That is very helpful thanks, now I just have to incorporate it into my sheet that has frozen panes, but as it is it shows the range as seen in the un frozen area no problem.
|
#8
|
||||
|
||||
Quote:
Try: ActiveWindow.ActivePane.VisibleRange.Cells(1, 1).EntireColumn.Address
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
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 |