Get the Extreme Left and Right Cell addresses-  Part II

Posted on 2012-08-16
Last Modified: 2012-08-16
Question by:Rayne

    Author Comment

    Hello All,

    For your excellent feedbacks: I want 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 way 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.

     if you can come up with different approaches to resolve this issue.
    LVL 33

    Expert Comment


    You still haven't explained how the correct row can be found.

    Is there a particular value to look for?

    Perhaps a format?

    Or perhaps we need to find a value first then move up/down a certain no of rows?
    LVL 8

    Assisted Solution

    by:Elton Pascua
    Another approach. This is with the assumption that there is no other instance of "C1" in the sheet.

    Sub TopColumns2()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim lVal As String
        Dim lRange As Range
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Sheet1")
        lVal = "C1"
        On Error GoTo ErrHandler
        Set lRange = ws.UsedRange.Find(What:=lVal, LookAt:=xlWhole)
        MsgBox "Left header cell: " & """" & WorksheetFunction.Substitute(lRange.Address, "$", "") & """" _
                & vbCrLf & "Right header cell: " & """" & WorksheetFunction.Substitute(lRange.End(xlToRight).Address, "$", "") & """"
        Exit Sub
    MsgBox (lVal & " not found.")
    End Sub

    Open in new window

    LVL 24

    Accepted Solution

    OK, here are 4 Functions for determining the first and Last rows and columns on a sheet:
    these should be more robust than the rather volatile UsedRange method.

    Private Function FirstRow(TheWorksheet As Worksheet) As Long
    If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
        FirstRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    End If
    End Function
    Private Function FirstColumn(TheWorksheet As Worksheet) As Long
    If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
        FirstColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
    End If
    End Function
    Private Function LastRow(TheWorksheet As Worksheet) As Long
    If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
        LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    End Function
    Private Function LastColumn(TheWorksheet As Worksheet) As Long
    If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
        LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If
    End Function
    Sub test()
    Dim ws As Worksheet
    Dim TopLeftC As Range
    Dim TopRightC As Range
    Set ws = ActiveSheet
    lRow = LastRow(ws)
    lCol = LastColumn(ws)
    fRow = FirstRow(ws)
    fCol = FirstColumn(ws)
    Set TopLeftC = ws.Cells(fRow, fCol)
    Set TopRightC = ws.Cells(fRow, lCol)
    TopLeft = Replace(TopLeftC.Address, "$", "")
    TopRight = Replace(TopRightC.Address, "$", "")
    MsgBox ("Left Header Cell : '" & TopLeft & "'" & vbCrLf & "Right Header Cell: '" & TopRight & "'")
    End Sub

    Open in new window


    Author Comment

    Perfection to the ultimate.
     Awesome, this is great. I love all your feedbacks. Now I have got a robust collection of function I use reliably. Thank you all for the help :)

    Author Comment

    Thank you The Barman, this was greatly needed :)
    Now its all dynamic

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    734 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

    21 Experts available now in Live!

    Get 1:1 Help Now