Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2018, 05:55 PM
trevorc trevorc is offline Return nameof Left column in current view Windows 7 32bit Return nameof Left column in current view Office 2013
Competent Performer
Return nameof Left column in current view
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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.
Reply With Quote
  #2  
Old 09-19-2018, 06:53 PM
macropod's Avatar
macropod macropod is offline Return nameof Left column in current view Windows 7 64bit Return nameof Left column in current view Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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
  #3  
Old 09-19-2018, 07:12 PM
trevorc trevorc is offline Return nameof Left column in current view Windows 7 32bit Return nameof Left column in current view Office 2013
Competent Performer
Return nameof Left column in current view
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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...
Reply With Quote
  #4  
Old 09-19-2018, 07:30 PM
macropod's Avatar
macropod macropod is offline Return nameof Left column in current view Windows 7 64bit Return nameof Left column in current view Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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]
Reply With Quote
  #5  
Old 09-19-2018, 07:58 PM
trevorc trevorc is offline Return nameof Left column in current view Windows 7 32bit Return nameof Left column in current view Office 2013
Competent Performer
Return nameof Left column in current view
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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 range, it shows the oname.name = <method 'range' of "object_global' failed>
hovering over oname shows it as oName.Name = "_xlfn.COUNTIFS"
oRng = nothing

perhaps oName is not pickup any names in the Activewookbook.Names
Reply With Quote
  #6  
Old 09-19-2018, 08:08 PM
NoSparks NoSparks is offline Return nameof Left column in current view Windows 7 64bit Return nameof Left column in current view Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

this of any use ?
Code:
Sub Testing_1()
MsgBox ActiveWindow.VisibleRange.Address
End Sub
Reply With Quote
  #7  
Old 09-19-2018, 08:27 PM
trevorc trevorc is offline Return nameof Left column in current view Windows 7 32bit Return nameof Left column in current view Office 2013
Competent Performer
Return nameof Left column in current view
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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.
Reply With Quote
  #8  
Old 09-19-2018, 08:38 PM
macropod's Avatar
macropod macropod is offline Return nameof Left column in current view Windows 7 64bit Return nameof Left column in current view Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

Quote:
Originally Posted by trevorc View Post
Thanks again,
But after copying your code as is nothing happened
...
perhaps oName is not pickup any names in the Activewookbook.Names
Well you keep referring to names, without clarifying whether you're referring to a named range or a column's designation. Some clarity wouldn't go astray.

Try:
ActiveWindow.ActivePane.VisibleRange.Cells(1, 1).EntireColumn.Address
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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
Return nameof Left column in current view 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
Return nameof Left column in current view 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
Return nameof Left column in current view How to return to where you left off? Shift+F5 not working. WaltR Word 2 12-24-2010 11:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft