Counting Merged Cells

[Whois Clinton]

I am using 2003 and counting colored blank cells. The range is several rows
with some rows containing 4 column cells (ie A1, B1, C1, D1) and other rows
with only 2 column cells having merged A1 and B1 into one cell with C1 and D1
into a second merged cell.
I need the merged cells to only count as one. Currently they count as 2
when I run the following:

Sub standard()
Set myrange = Range("A120")
For Each c In myrange
If c.Interior.ColorIndex = 6 Then
yellowcells = yellowcells + 1
End If
Next
MsgBox yellowcells
End Sub


I am not an expert macro writer so I hope this makes sense :J
Thanks in advance for any help.

Clint

 

[Jim Cone]

Clint,
Add "Option Explicit" as the first line in your module then
give this a try...
'--
Sub standardCanKill()
Dim c As Range
Dim MyRange As Range
Dim arrRng() As String
Dim yellowCells As Long
Dim N As Long
Dim M As Long

Set MyRange = Range("A120")
ReDim arrRng(1 To MyRange.Count)

For Each c In MyRange
If c.Interior.ColorIndex = 6 Then
If c.MergeCells Then
N = N + 1
For M = 1 To N
If c.MergeArea.Address = arrRng(M) Then
Exit For
End If
Next
If M > N Then
yellowCells = yellowCells + 1
arrRng(N) = c.MergeArea.Address
End If
Else
yellowCells = yellowCells + 1
End If
End If
Next
MsgBox yellowCells
Set c = Nothing
Set MyRange = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Whois Clinton"
<[email protected]>
wrote in message
I am using 2003 and counting colored blank cells. The range is several rows
with some rows containing 4 column cells (ie A1, B1, C1, D1) and other rows
with only 2 column cells having merged A1 and B1 into one cell with C1 and D1
into a second merged cell.
I need the merged cells to only count as one. Currently they count as 2
when I run the following:

Sub standard()
Set myrange = Range("A120")
For Each c In myrange
If c.Interior.ColorIndex = 6 Then
yellowcells = yellowcells + 1
End If
Next
MsgBox yellowcells
End Sub

I am not an expert macro writer so I hope this makes sense :J
Thanks in advance for any help.
Clint

 

[Whois Clinton]

WOW That works even without adding the "Option Explicit". How about if I
didn't care what the color was just whether or not there was a color? This
way I wouldn't have to copy and rewrite this for every color. Thanks so much
already!

 

[Jim Cone]

You are welcome.
The use of Option Explicit in all modules is strongly recommended.
It acts like "Lassie" who would start barking and then herd you away from the
hidden mine shaft you were about to fall into. To add it automatically to all modules...
In the VBE go to Tools | Options | Editor (tab) and checkmark the
"Require Variable Declaration" button.

As far as checking for any colored background in a cell, you can simply
determine if the cell interior has no color...
Change...
If c.Interior.ColorIndex = 6 Then
To...
If c.Interior.ColorIndex <> xlColorIndexNone Then
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Whois Clinton"
<[email protected]>
wrote in message
WOW That works even without adding the "Option Explicit". How about if I
didn't care what the color was just whether or not there was a color? This
way I wouldn't have to copy and rewrite this for every color. Thanks so much
already!

 

[Whois Clinton]

Jim,
I cannot thank you enough for your help. I have been counting up to 1500
cells in 25 workbooks and was anticipating another 160 workbooks every year.
You have saved me weeks of counting and also made me look quite computer
savy. Everything you recomended worked and worked well.

Thanks again!!!
Clinton

 

[Jim Cone]

Clinton,
The feedback is appreciated.
A note... If you were to start running the code over large ranges -
thousands of rows/dozens of columns - then the code would need tweaking
to reduce the memory load. The string array construction is not optimized.
Sincerely,
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Whois Clinton"
<[email protected]>
wrote in message
Jim,
I cannot thank you enough for your help. I have been counting up to 1500
cells in 25 workbooks and was anticipating another 160 workbooks every year.
You have saved me weeks of counting and also made me look quite computer
savy. Everything you recomended worked and worked well.
Thanks again!!!
Clinton

 

[Whois Clinton]

No problem, the cells are in clusters of 50-180 throughout a worksheet, so I
should be ok. Thanks again.