Get the Extreme Left and Right Cell addresses

Posted on 2012-08-16
Last Modified: 2012-08-16
Hello Everyone,

I am looking for a code that does this. I am tired to manually mentioning the first and the last column header address in all of my functions so need this macro to grab these extreme left – right cell address. Now..
What it does >> it looks at sheet1  - the very first row that has the column names – searches left and right….when it searches left, it grabs c1’ cell address (B3)  - when it searches extreme right, it gets the c9’s cell address and finally give s a msgbox saying :
Left Header Cell : “B3”
Right Header Cell: “J3”

Please note that the first row that has the column names can be at row 3 or any other row…so it has to do some type of search or something
Question by:Rayne
    LVL 24

    Accepted Solution

    One way to do it:

    Sub TopColumns()
    Dim uRange As Range
    Set uRange = ActiveSheet.UsedRange.Rows(1)
    TopLeft = Replace(Left(uRange.Address, InStr(1, uRange.Address, ":", vbTextCompare) - 1), "$", "")
    TopRight = Replace(Right(uRange.Address, Len(uRange.Address) - InStr(1, uRange.Address, ":", vbTextCompare)), "$", "")
    MsgBox ("Left Header Cell : '" & TopLeft & "'" & vbCrLf & "Right Header Cell: '" & TopRight & "'")
    End Sub

    Open in new window

    LVL 33

    Expert Comment


    How can you tell which row is the row with headings?
    LVL 8

    Expert Comment

    by:Elton Pascua
    What are the extreme left and extreme right headings called? You said the row would vary but would the columns always be the same?

    Author Comment

    Hello All,

    For your excellent feedbacks: I will be opening a second question follow to this and get all other different approaches towards this issue.

    The_Barman: Thank your solution works for me. you used used range. I would now want to see if there is nay other approach (s) different from this method.

    imnorie: How can you tell which row is the row with headings?

    The top most row is most likely to be the row with headings - now the top-most row could be on row3 or row 5 or any row


     What are the extreme left and extreme right headings called? You said the row would vary but would the columns always be the same?
    C1, C9
    Actually, the users would be adding in more columns at the end of the table, so the last column will change with time.

    I have posted another question for you guys > if you can come up with different approaches to resolve this issue.

    Author Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now