I'm trying to check equality between cells in an irregular, non-continuous range of cells. For this purpose I would like to know if all cells are equal to the first
Cell in the
Range can be anything, but let's say it could be the following (don't mind the implicit reference for now):
Range is non-continuous, there is no way to use the
COUNTIF as a
WorksheetFunction, nor can I implement some sort of
Evaluate. So I was looking for the easiest way to do this. I came up with the following:
'Get a 1D-array from columns of interest Dim arr As Variant: arr = Application.Index(Range("A1:AA1").Value, 1, Array(1, 2, 3, 6, 11, 24, 27)) 'Join the 1D-array and check against 7* the value of A1 If Join(arr, "") = Replace("???????", "?", Range("A1").Value) Then Debug.Print "All equal to first cell" Else Debug.Print "Not all equal to first cell" End If
I've spent quite some time trying to come up with some code that would avoid iteration over the
Cells (or iteration of any type), for example like below:
'Get dictionary ready Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") 'Get range of cells Dim rng As Range: Set rng = Range("A1:C1,F1,K1,X1,AA1") 'Iterate over cells For Each cl In rng dict(cl.Value) = 1 Next cl 'Test for same values If dict.Count = 1 Then Debug.Print "All equal to first cell" Else Debug.Print "Not all equal to first cell" End If
It has also become clear to me that there is no simple
COUNTIF of an
Array. And also something like
Filter has proven to not be "error" proof.
While this is working code, I was hoping to find out the best way to know if all values in an irregular, non-continuous range of cells are the same. And therefor hope CR is the right place to ask.