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

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


"Mr. Smith" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
> 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
>
>
>
>
>
>

You could use this approach

If
ActiveSheet.Evaluate("SUMPRODUCT((A2:A20<>"""")/COUNTIF(A2:A20,A2:A20&""""))")
> 1 Then
MsgBox "yes"
Else
MsgBox "no"
End If


--
HTH

Bob

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

"Mr. Smith" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
>
>
>

Nice twist Cordially!

Thanks!
Mr. Smith

"papou" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
> "Mr. Smith" <(E-Mail Removed)> a écrit dans le message de news:
> (E-Mail Removed)...
>> 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
>>
>>
>>
>>
>>
>>
>
>

Just test if the MAX equals the MIN.
--
Gary''s Student - gsnu2007


"Mr. Smith" wrote:

> 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
>
>
>
>
>
>
>

Very elegant Gary's

Kind regards
Mr. Smith

"Gary''s Student" <(E-Mail Removed)> wrote in message
news:17B7A82C-34DF-433C-AF12-(E-Mail Removed)...
> Just test if the MAX equals the MIN.
> --
> Gary''s Student - gsnu2007
>
>
> "Mr. Smith" wrote:
>
>> 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
>>
>>
>>
>>
>>
>>
>>