Counting Bordered Cells

[hddofut]

Complete VBA noob.

I have created a module in VBA and have tried the following to count cells in a range on one sheet that have four sides bordered and have the count results in a cell on another sheet.
Line style and color do not matter.

Function CountBrd(rng As Range)

Dim i As Integer
Dim cell As Range
i = 0
For Each cell In rng

If cell.Borders(xlEdgeLeft).LineStyle <> xlLineStyleNone
AndIf cell.Borders(xlEdgeRight).LineStyle <> xlLineStyleNone
AndIf cell.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone
AndIf cell.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone

Then
i = i + 1
End If
Next

CountBrd = i

End Function

On another sheet in a cell I have =CountBrd("Sheetname""Range")

I get a couple of syntax errors and since I know zero about this I come to you for help.

 

[Claus Busch]

Hi,

Am Sun, 25 May 2014 21:06:37 -0700 (PDT) schrieb (e-mail address removed):

I have created a module in VBA and have tried the following to count cells in a range on one sheet that have four sides bordered and have the count results in a cell on another sheet.
Line style and color do not matter.

try:

Function CountBrd(myRng As Range) As Long
Dim rngC As Range
Dim i As Long

For Each rngC In myRng
If rngC.Borders(xlEdgeLeft).LineStyle _
<> xlNone Then
i = i + 1
End If
Next
CountBrd = WorksheetFunction.RoundUp(i / 2, 0)
End Function

and call this function into the sheet with:
=CountBrd(A1:E15)
or from another sheet with:
=CountBrd(Sheet2!A1:E15)


Regards
Claus B.

 

[sbwhitney]

Complete VBA noob.



I have created a module in VBA and have tried the following to count cells in a range on one sheet that have four sides bordered and have the count results in a cell on another sheet.

Line style and color do not matter.



Function CountBrd(rng As Range)



Dim i As Integer

Dim cell As Range

i = 0

For Each cell In rng



If cell.Borders(xlEdgeLeft).LineStyle <> xlLineStyleNone

AndIf cell.Borders(xlEdgeRight).LineStyle <> xlLineStyleNone

AndIf cell.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone

AndIf cell.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone



Then

i = i + 1

End If

Next



CountBrd = i



End Function



On another sheet in a cell I have =CountBrd("Sheetname""Range")



I get a couple of syntax errors and since I know zero about this I come to you for help.

Thanks Claus,

When I run the function I do receive a result, but it is not accurate. IT may be counting the cells that have a left border by default.
This may be helpful, by default the range will have the top and left cell already with a border. I need to count the cells in the range when they have all four sides bordered.

I see this line " If rngC.Borders(xlEdgeLeft).LineStyle _" is that looking at only cells with a left border?

 

[Claus Busch]

Hi,

Am Mon, 26 May 2014 08:25:39 -0700 (PDT) schrieb (e-mail address removed):

When I run the function I do receive a result, but it is not accurate. IT may be counting the cells that have a left border by default.
This may be helpful, by default the range will have the top and left cell already with a border. I need to count the cells in the range when they have all four sides bordered.

yes, you are right
Try:

Function CountBrd(myRng As Range) As Long
Dim rngC As Range
Dim i As Long

For Each rngC In myRng
If rngC.Borders(xlEdgeLeft).LineStyle <> xlNone _
And rngC.Borders(xlEdgeBottom).LineStyle <> xlNone _
And rngC.Borders(xlEdgeRight).LineStyle <> xlNone _
And rngC.Borders(xlEdgeTop).LineStyle <> xlNone Then
i = i + 1
End If
Next
CountBrd = i
End Function


Regards
Claus B.

 

[Peter T]

Complete VBA noob.

I have created a module in VBA and have tried the following to count cells
in a range on one sheet that have four sides bordered and have the count
results in a cell on another sheet.
Line style and color do not matter.

Another one, based on Claus' but could be a few times faster be faster with
a large range

Function CountBordersAround(rng As Range) As Long
Dim n As Long
Dim c As Range
n = rng.Count
For Each c In rng
For i = xlEdgeLeft To xlEdgeRight
If c.Borders(i).LineStyle = xlNone Then
n = n - 1
Exit For
End If
Next
Next
CountBordersAround = n
End Function

Regards,
Peter T

 

[hddofut]

Another one, based on Claus' but could be a few times faster be faster with

a large range



Function CountBordersAround(rng As Range) As Long

Dim n As Long

Dim c As Range

n = rng.Count

For Each c In rng

For i = xlEdgeLeft To xlEdgeRight

If c.Borders(i).LineStyle = xlNone Then

n = n - 1

Exit For

End If

Next

Next

CountBordersAround = n

End Function



Regards,

Peter T

Wooot,Thank you Claus and Peter!
Both work well.