Get the Extreme Left and Right Cell addresses- Part II

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
NorieAnalyst Assistant Commented:

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?
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveCost AccountantCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.