The code below should do it for you. Change the column identified as
testColumn to be one that would at the very least have an entry in the row
that is bordered at the bottom. It could have more data on down into the
unbordered area, that's fine.
What it does: it finds the last row in that column that contains a non-empty
cell. It then starts looking from there up the sheet until it finds a cell
with a border on its lower/bottom edge. At that point it scrolls down to the
row just below the bordered row and leaves you at the first row outside of
the bordered area. From there, what you do is up to you. If it doesn't find
a cell with a bottom border, then it tells you that and quits without doing
anything else.
To use it: open your workbook and press [Alt]+[F11] and then choose Insert |
Module from within the VB Editor. Copy the code below and paste it into the
empty module presented to you and change the value of testColumn as needed.
Close the VB Editor and save the workbook. To run it, choose the sheet with
the data of interest on it and use Tools | Macro | Macros and choose its name
from the list and click the [Run] button. Just that easy.
The code:
Sub FindLastBorderedCell()
'define this as a column that will
'have data at least to the last row
'that would have a border, or even beyond
Const testColumn = "E" ' change for your data
Dim lastRow As Long
Dim topCell As Range
Set topCell = Range(testColumn & 1) ' top of column
lastRow = Range(testColumn & Rows.Count).End(xlUp).Row
'work up from the bottom until we hit
'a cell that has a border on the bottom edge.
Do While lastRow > 1
lastRow = lastRow - 1
If topCell.Offset(lastRow, 0).Borders _
(xlEdgeBottom).LineStyle <> xlNone Then
'found a cell with border on lower edge
'adjust pointer to 1 row below bordered cell
'and scroll down to column A on that row
Application.Goto Range("A" & lastRow + 2), True
Set topCell = Nothing ' release resource
Exit Do ' quit looking
End If
If lastRow = 1 Then
MsgBox "Could not find cell with lower edge border.", _
vbOKOnly, "Operation Terminated"
Set topCell = Nothing ' release resource
Exit Sub
End If
Loop
End Sub