Count Contiguous cells only

[YasserKhalil]

Hello everyone
I have some data in columns("B:R") ..
The required in column A is to type Yes or No according to one criteria which is :
If there are 6 or more adjacent cells which has data in them then type Yes in column A

For example: in row 1 Range("I1:N1") all cells have data so the criteria is achieved so type "Yes" in A1

In Row 2 there is no range achieved the criteria so type in A2 the word "No"

I prefer UDF function for that task
Thanks advanced

[YasserKhalil]

I tried this solution

Please Login or Register  to view this content.

It works well in VBA

Please Login or Register  to view this content.

It gives me False while when using this UDF function in worksheet it gives me True

Please Login or Register  to view this content.

[romperstomper]

Specialcells doesn't work in UDFs.

[shg]

Specialcells doesn't work in UDFs.

Neither does DisplayFormat. We need a list -- what else you got, Rory?

[shg]

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot everyone
Mr shg I tested your udf but I got False for all ranges ...
Thanks for help

Please Login or Register  to view this content.

[shg]

Row\Col	A	B	C	D	E	F	G
1	6	6	2	3		TRUE	F1: =SomeGE6(A1:D1)
2	3	4	5	5		FALSE
3	6	1	5	3		TRUE
4	3	2	2	2		FALSE
5	2	1	5	6		TRUE
6	2	4	3	5		FALSE

[Leith Ross]

Hello YasserKhalil,

This worked for me. The first argument "Rng" is the range of row values to check. The second argument is the value that must occur at least six times contiguously. In your example cell "A1" would have the formula =IsCountGTE6(B1:R1, 1).

Please Login or Register  to view this content.

[YasserKhalil]

@Mr. shg
Thanks alot for help .. your UDF function would be useful for another issue ..

@Leith Ross
Great and awesome UDF .. Than you very much for this wonderful solution
Just a little modification... I need to do without the second argument which specify the value that must occur
I just need six adjacent cells that have constants (never mind the data itself)

Thanks again for this great help

[YasserKhalil]

Thanks I could manage it

Please Login or Register  to view this content.

[Leith Ross]

Hello YasserKhalil,

You're welcome.

[shg]

@Mr. shg
Thanks alot for help .. your UDF function would be useful for another issue.

You're welcome, but I should have read the question more closely. =MAX(range)>=6 is all you'd need to replace my UDF.

[YasserKhalil]

Thank you Mr. Leith
Is it possible to do that task by formulas .. Not by VBA at all?

[shg]

Sure:

Row\Col	A	B	C	D	E
1	0		1	FALSE	D1: {=MAX(FREQUENCY(IF(A1:A23=C1, ROW(A1:A23)), IF(A1:A23<>1, ROW(A1:A23)))) > 6}
2	1
3	1
4	1
5	0
6	1
7	1
8	1
9	1
10	0
11	1
12	1
13	0
14	1
15	1
16	1
17	1
18	0
19	1
20	1
21	0
22	1
23	1

[romperstomper]

@shg
Currentarray and currentregion spring to mind.

[shg]

Gracias. Also FindNext.

Row\Col	A	B	C	D	E
1		2003	2007	2010	2013
2	Range.CurrentArray	X	X	X	?
3	Range.CurrentRegion	X	X	X	?
4	Range.DisplayFormat	Didn’t exist	Didn’t exist	X	?
5	Range.FindNext	X	X	X	?

Anything else come to mind?

[romperstomper]

Not offhand. This is one of those topics I keep meaning to blog about - unfortunately don't have the time to research it properly just now. BTW none of them work in 2013/16 either as far as I know.

Oh - you missed off SpecialCells...

[shg]

Thanks, Rory. My table is your table; blog away.

Row\Col	A	B	C	D	E	F
1	Property/Method	2003	2007	2010	2013	2016
2	Range.CurrentArray	X	X	X	X	X
3	Range.CurrentRegion	X	X	X	X	X
4	Range.DisplayFormat	Didn’t exist	Didn’t exist	X	X	X
5	Range.FindNext	X	X	X	X	X
6	Range.SpecialCells	X	X	X	X	X

[shg]

Application.OnTime also, methinks.