Get the Extreme Left and Right Cell addresses- Part II

Who is Participating?
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

RayneAuthor Commented:
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.
NorieVBA ExpertCommented:

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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Elton PascuaCommented:
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

RayneAuthor Commented:
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 :)
RayneAuthor Commented:
Thank you The Barman, this was greatly needed :)
Now its all dynamic
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.