Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Get the Extreme Left and Right Cell addresses- Part II

2 Solutions
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.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now