Count all adjoining cells with the same value

rubbis_with_excel

New Member
Joined
Jan 19, 2004
Messages
28
Hi,

I've been looking for some code or a formula to count surrounding cells with the same value. I've found some examples, but I need something a bit more complex so I was hoping someone clever here can help me.

I'm developing a silly little game and I want to be able to recognise the whole surrounding block of same-value cells

Using the example below, I want to be able to recognise/count the lower block of A's and delete them, but not the row of A's at the top (I hope that makes sense!).

A A A A
B B C C
B A A B C
B A A A B C
B A A A A B


Every code or formula I've found either doesn't pick up enough of the A's or finds absolutely everything!

Any help would be appreciated, thanks
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Perhaps:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG20Feb59
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] ActiveSheet.Range("A1").CurrentRegion
    [COLOR="Navy"]If[/COLOR] Dn.Value = "A" And Not Dn.Row = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1: Dn.Value = ""
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
 MsgBox "There were  " & c & " ""A'[COLOR="Green"][B]s"" Deleted"[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

rubbis_with_excel

New Member
Joined
Jan 19, 2004
Messages
28
Hi, thanks for the reply.

That solution is super quick!!! However, when I said that the top row shouldn't get counted, I meant because it's not part of that continuous block of A's rather than to always exclude the top row.

I want to be able to identify every "A" cell that is touching another "A" cell, as long as there are more than 4 in a group

Maybe I'm over-thinking this too much!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,585
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top