| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if all values in a column is identical
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if all values in a column is identical
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" a écrit dans le message de news: ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if all values in a column is identical
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if all values in a column is identical
Nice twist Cordially!
Thanks! Mr. Smith "papou" wrote in message ... 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" a écrit dans le message de news: ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if all values in a column is identical
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if all values in a column is identical
Very elegant Gary's
Kind regards Mr. Smith "Gary''s Student" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If value is true the check column for specific values | Excel Discussion (Misc queries) | |||
| Merging files with nearly identical values in one column | Excel Discussion (Misc queries) | |||
| How to check for duplicate values within a column | Excel Programming | |||
| check value in cells against all values in a column | Excel Programming | |||
| Check for list of 3 values (alpha) in a column | Excel Programming | |||
![Mr. Smith[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
