Check non-continuous range for equal values Background

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. The Range can be anything, but let's say it could be the following (don't mind the implicit reference for now):

Range("A1:C1,F1,K1,X1,AA1")




Sample Code:

Because the Range is non-continuous, there is no way to use the COUNTIF as a WorksheetFunction, nor can I implement some sort of INDIRECT within 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




Question:

I've spent quite some time trying to come up with some code that would avoid iteration over the Range object's 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.
Using a Dictionary to count the occurences of a single value is overkill.  If the question was count the number of values that match the first then If FirstValue = cl.Value Then Count = Count + 1 would be far more efficient than using a Dictionary.


  I would like to know if all cells are equal to the first Cell in the Range


It seems to me that you are over complicating things.  Based on the objective above we are looking for a simple True or False answer.  Currently, you have to iterate over all the cells before you can determine whether all the cell value match. So it takes the same amount of time whether all cells match or not.  It would be far better to set a flag and exit the loop when a unmatched value is found.

'Get range of cells
Dim rng As Range
Set rng = Range("A1:C1,F1,K1,X1,AA1")

Dim Value As Variant
Value = rng.Cells(1, 1).Value
Dim flag As Boolean

For Each cl In rng
    If cl.Value &lt;&gt; Value Then
        flag = True
        Exit For
    End If
Next cl

'Test for same values
If Not flag Then
    Debug.Print "All equal to first cell"
Else
    Debug.Print "Not all equal to first cell"
End If