Solved

# Get the Extreme Left and Right Cell addresses-  Part II

Posted on 2012-08-16
324 Views
0
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

techfanatic
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.
0

LVL 33

Expert Comment

Rayne

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

LVL 8

Assisted Solution

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

ErrHandler:

End Sub
``````
0

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)

MsgBox ("Left Header Cell : '" & TopLeft & "'" & vbCrLf & "Right Header Cell: '" & TopRight & "'")

End Sub
``````
Last-Cells.xlsm
0

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

Author Comment

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

## Featured Post

### Suggested Solutions

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.