ExcelBanter - I want to select only cells with a border

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

"dadouza" wrote:

I have a lot of sheets that have some information in a bordered section of
the sheet, and some information that is not bordered. I want to delete the
non bordered section, sometimes this will be after row 7, and sometimes after
row 700. So I need a quick way of selecting the bordered, or non bordered,
section of the sheet without having to scroll down to the bottom of the sheet
every time. I don't mind if it's done with a few mouse clicks, or an add-in,
or a macro, I just need to be able to do it.

Thanks for looking at my post