View Single Post
 
Old 08-13-2015, 01:02 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Hello tdody,

I added the following macro to your file and saved it as a macro enabled .xlsm file.

I believe Office 2007 and newer install by default with macros disabled and no notification letting you know.
If your Excel is still like that this won't work without adjustments in the Trust Center.
Here's a couple of links if you need guidance concerning this.
http://www.addictivetips.com/windows...in-excel-2010/
https://support.office.com/en-nz/art...1-9efe4f860b12

Alt + F8 will bring up the Macro Dialogue.
Click Count_The_Areas and hit Run.

Code:
Sub Count_The_Areas()
    Dim rng As Range
    Dim cel As Range
    Dim lastrow As Long
    Dim i As Long
    
With ActiveSheet
    'find last used row
    lastrow = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious).Row
    'set the range to look at
    Set rng = .Range("A1:A" & lastrow)
    'look at the individual cells in that range
    For Each cel In rng
        'check the cell for a line formatted to the bottom of the cell
        If Not cel.Borders(xlEdgeBottom).LineStyle = xlNone Then
            'that means it does have a bottom line, so add to i
            i = i + 1
        End If
    Next cel
    
    MsgBox "There are " & i & " areas between borders."
    
End With

End Sub
Attached Files
File Type: xlsm TEST Spread Sheet.xlsm (19.6 KB, 8 views)
Reply With Quote