Check if all values in a column is identical

M

Mr. Smith

Hi.
(VBA scripting in Excel 2003)

Case:
If all values in column A are identical, Then
No need to activate SUBTOTAL on the data.
Else
Activate SUBTOTALS.
End if

What would be a "slick" way of checking column A for changing values? The
smartes way I can think of is something like this

chkstr = Range("A2")
For i = 3 to LastRowOfData 'Data starts in row 2, row 1 contains heading
If Cells("A"&i) <> chkstr Then
GoTo: InsertSubtotals
Next i

'Not even sure if it would work.....

Kind regards
Mr. Smith
 
P

papou

Hello Mr. Smith
Here's a suggestion (dunno if its smarter!)
Amend with the relevant range reference:
'if identical values found exit the sub
If Evaluate("COUNTIF(A2:A76,A2)=COUNTA(A2:A76)") Then Exit Sub

HTH
Cordially
Pascal
 
B

Bob Phillips

You could use this approach

If
ActiveSheet.Evaluate("SUMPRODUCT((A2:A20 said:
MsgBox "yes"
Else
MsgBox "no"
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Mr. Smith

Nice twist Cordially!

Thanks!
Mr. Smith

papou said:
Hello Mr. Smith
Here's a suggestion (dunno if its smarter!)
Amend with the relevant range reference:
'if identical values found exit the sub
If Evaluate("COUNTIF(A2:A76,A2)=COUNTA(A2:A76)") Then Exit Sub

HTH
Cordially
Pascal
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top