Determining if values in a column range are of same value.

[Guest]

Hello,

Was wondering if there is a built in command or more efficent way to
determine if values in a column range are the same.

The simplest, but maybe not the most efficient way i can think of is to do:

activesheet.Range("a1").Formula = "=Min('sheet1'!B1:B1000)"
activesheet.Range("a2").Formula = "=Max('sheet1'!B1:B1000)"
if activesheet.Range("a1").value = activesheet.Range("a2").value then
'values in column range are the same.
end if

Anyone think of a more efficient way of dong this? Does excel have some sort
of similar built in function?

Thanks!

 

[Leo Heuser]

Hello Bing

One way:

If Evaluate("Min(B1:B1000)") = Evaluate("Max(B1:B1000)") Then
MsgBox "All the same"
End If

 

[Tom Ogilvy]

Most of the worksheet functions are available in VBA:

if application.Min(Range("Sheet1").Range("B1:B1000")) = _
application.Max(Range("Sheet1").Range("B1:B1000")) then

 

[Bob Phillips]

Another way

If application.countif(range("B1:B1000"),range("B1")) =
Range("B1:B1000").Cells.Count
' same

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks! you guys are great!

Another way

If application.countif(range("B1:B1000"),range("B1")) =
Range("B1:B1000").Cells.Count
' same

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

determine if values in a column range are the same.

If they are all numbers, perhaps another option:

If [STDEV(A1:B1000)] = 0 Then MsgBox "All same numbers"