Scan 8 adjacent cells

[methody]

Hi
I have a range with 200 cells -20 row and 10 columns. Each cell is either blank or has a unique number in the cell. Moving across each row, there can be a maximum of 8 adjacent non-empty cells.
I am looking for a macro that will scan the range of cells, look for the instances of 8 adjacent cells and return the unique number which is in the leftmost cell of each of the cases of 8 adjacents.

As if that isn't tricky enough i am also looking for a separate list for the leftmost cell where there are only 7 adjacents. Here it would be where there are 7 only. I appreciate that this is difficult as there may be overlap.

Any help appreciated. sorry if this isn't clear.

 

[Fluff]

Are the numbers hard values, or the result of a formula?

 

[methody]

hard values

 

[Fluff]

How about

Sub methody()
   Dim Rw As Range, Ar As Range
   
   For Each Rw In Range("A1:J20").Rows
      For Each Ar In Rw.SpecialCells(xlConstants).Areas
         If Ar.Count = 7 Then
            Range("N" & Rows.Count).End(xlUp).Offset(1).Value = Ar(1).Value
         ElseIf Ar.Count = 8 Then
            Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Ar(1).Value
         End If
      Next Ar
   Next Rw
End Sub

I've assumed that your numbers are in A1:J20

 

[JamesCanale]

No VB approach

MrExcelPlayground.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1											8 blanks	7 blanks
2	453	45		65		21					None	None
3	4										4	4
4	243								541	21	None	243
5	32										32	32
6	5										5	5
7	54			65							None	None
8		1245									1245	1245
9	56	21								4	None	56
10											None	None
11											None	None
12	43										43	43
13	75	32								1	None	75
14	86										86	86
15						45					None	None
16											None	None
17	57										57	57
18											None	None
19	345									12	345	345
20											None	None
21	345								12		None	345
Sheet23

Cell Formulas
Range	Formula
K2:K21	K2	=IF(OR(SUMPRODUCT(--(B2:I2=""))=8,SUMPRODUCT(--(C2:J2=""))=8),IF(A2="",IF(B2="","None",B2),A2),"None")
L2:L21	L2	=IF(OR(SUMPRODUCT(--(B2:H2=""))=7,SUMPRODUCT(--(C2:I2=""))=7,SUMPRODUCT(--(D2:J2=""))=7),IF(A2="",IF(B2="",IF(C2="","None",C2),B2),A2),"None")

 

[Fluff]

@JamesCanale
I think you missed this bit of the op

8 adjacent non-empty cells.

 

[JamesCanale]

@JamesCanale
I think you missed this bit of the op

Ooops. Nevermind...

 

[Fluff]

This will put the starting numbers in the same row as the data

Sub methody()
   Dim Rw As Range, Ar As Range
   
   For Each Rw In Range("A1:J20").Rows
      For Each Ar In Rw.SpecialCells(xlConstants).Areas
         If Ar.Count = 7 Then
            Range("N" & Rw.Row).Value = Ar(1).Value
         ElseIf Ar.Count = 8 Then
            Range("M" & Rw.Row).Value = Ar(1).Value
         End If
      Next Ar
   Next Rw
End Sub

 

[methody]

Hello Fluff
IThank you. I think you are close but I can't get any response when I run your code. For the grid below I have shown the result I am looking for on the right

THank you

 

[Fluff]

Is your data in A1:J20?
And was that the activesheet when you ran the macro?