Identify adjacency cells with same value

[Paul D Smith]

Is there a simple way to identify all the adjacency cells (in a row or
column) with the same value? I can obviously do this with a loop but I'm
hoping there is a VBA function that might do this more quickly.

Thanks,
Paul DS.

 

[Auric__]

Is there a simple way to identify all the adjacency cells (in a row or
column) with the same value? I can obviously do this with a loop but I'm
hoping there is a VBA function that might do this more quickly.

How about this?

Sub matches()
Dim found As Collection
Set found = New Collection
v = ActiveCell.Value
If v = "" Then Exit Sub
For Each cell In Rows(ActiveCell.Row).Cells
If cell.Column > ActiveCell.SpecialCells(xlCellTypeLastCell).Column _
Then Exit For
If cell.Value <> "" Then
If cell.Column <> ActiveCell.Column Then
If v = cell.Value Then
found.Add cell
End If
End If
End If
Next
For Each cell In Columns(ActiveCell.Column).Cells
If cell.Row > ActiveCell.SpecialCells(xlCellTypeLastCell).Row Then _
Exit For
If cell.Value <> "" Then
If cell.Row <> ActiveCell.Row Then
If v = cell.Value Then
found.Add cell
End If
End If
End If
Next
'do what you need here, then...
Set found = Nothing
End Sub

At the end of the sub, you have a collection called "found" that contains
each cell that matches ActiveCell.Value. You can use that to do whatever you
need -- for example, if you just need a list of addresses:

For n = 1 To found.Count
Debug.Print found.Item(n).Address
Next

Perhaps a bit over-complicated, but it works...

 

[Paul D Smith]

....snip...

That would certainly work but I was hoping to be able to avoid VBA loops if
possible as I find them generally slow. My problem is slightly simpler than
your general solution in that I know that:

1. I know the rows affected
2. All rows have data
3. All rows with identical data will be adjacent.

Currently I use a loop which keeps walking the rows whilst the row data
equals that in the first row (and then logically move the first row forward
when I find a mismatch) but I was hoping there was a more elegant solution.

Paul DS

 

[Paul D Smith]

...snip...

That would certainly work but I was hoping to be able to avoid VBA loops
if possible as I find them generally slow. My problem is slightly simpler
than your general solution in that I know that:

1. I know the rows affected
2. All rows have data
3. All rows with identical data will be adjacent.

Currently I use a loop which keeps walking the rows whilst the row data
equals that in the first row (and then logically move the first row
forward when I find a mismatch) but I was hoping there was a more elegant
solution.

Paul DS

Looking on the web, using "=EXACT()" seems to be a way to do this.
Basically set another column which says "Is this the same as the cell in the
next row" and then work on the "False" values which indicate a change.

Paul DS