Testing for contiguous cells

[jamieuk]

For a game, I need to determine whether non-blank cells in range
A1:J10 are orthogonally contiguous. If I had values in cells A1, A2,
A3, B3 and C3 then the result would be true. However, remove the value
from cell A3 and the result is now false. Any ideas how to do this
test?

 

[Tom Ogilvy]

if intersect(Range("A1").End(xldown).End(xltoRight),Range("A1:J10")) is
nothing then
if intersect(Range("A1").End(xltoRight).End(xldown),Range("A1:J10)) is
nothing then
msgbox "Not orthogonally contiguous"
end if
End if

of
if isemtpy(Range("A1").End(xldown).offset(0,1)) then
if isempty(Range("A1").End(xltoRight).Offset(1,0)) Then
msgbox "Not orthogonally contiguous"
end if
End if

 

[jamieuk]

Thanks Tom, I appreciate you taking the time to reply. Unfortunately,
I couldn't get either of your suggestions to work.

I took your second suggestion, because it takes account of range
A1:J10 (the 'board'). I need to test that every non-blank cell is
contiguous with all others (not just cell A1), so I put the code in a
loop:

Sub Test()

Dim oBoard As Excel.Range
Dim oSqaure As Excel.Range
Dim blnContiguous As Boolean

Set oBoard = Range("A1:J10")

blnContiguous = True

For Each oSqaure In oBoard
If Len(oSqaure.Value) > 0 Then
blnContiguous = IsContiguous(oSqaure, oBoard)
If Not blnContiguous Then
Exit For
End If
End If
Next

If Not blnContiguous Then
MsgBox "Not orthogonally contiguous"
End If

End Sub

Function IsContiguous(ByVal Square As Excel.Range, _
ByVal Board As Excel.Range) As Boolean

IsContiguous = True

If Intersect(Square.End(xlDown).End(xlToRight), Board) Is Nothing Then
If Intersect(Square.End(xlToRight).End(xlDown), Board) Is Nothing
Then
IsContiguous = False
End If
End If

End Function

When there are non-blank values in A1 and A2 only (obviously
contiguous), both combinations of xlDown and xlRight (for both cells)
end up off the board and IsContiguous incorrectly returns false.

An approach seems to be emerging i.e. count the cell immediately to
the left or below the current cell but also take account of whether
the current cell has already been counted. But it's making my head
hurt!

Any further suggestions would be most welcome.

--