Count the no. of items in data validation LIST [SOLVED]

[nagovind]

Dear All,


In cell A1 i have applied data validation => criteria allow => "List"


Source = AA,EE,C,Q,TT


Done


Requirement is how to find the no. of items in the list assigned in the cell A1 by data validation using VBA


I need to assign the COUNT of list items to a separate variable


In the above example the quantity is 5


How to arrive this using excel VBA


Please advise

 

[Luke M]

Assuming the data validation list is hardcoded, and not a reference to a range, this will work

[pre]

Sub ValidationCount()
Dim ItemCount As Long
Dim FormHolder
FormHolder = Split(Range("A1").Validation.Formula1, ",")
ItemCount = UBound(FormHolder) + 1
MsgBox "Number of items in validation: " & ItemCount
End Sub

[/pre]

 

[Hui]

Navogind

Instead of having your list in Data validation as = AA,EE,C,Q,TT

Why not put the values in AA,EE,C,Q,TT in say AA1:AA5

name that Range "List"


then in data Validation instead of using List: =AA,EE,C,Q,TT

Use List: =List


Then elsewhere you can also use =Counta(List)


If you use a named Formula for List like: =Offset($AA$1,,,Counta($AA$1:$AA$100),1)

Then the length of the list will change as items are added or removed and the count will also change

 

[nagovind]

@ Luke M,


Thank you for your reply

But after running the code it is showing the result as Number of items in validation:1

But it should be 5

Please advise

Thank you


-------

@ Hui,


Thank you for your reply

This is a existing template created by others, i need to work on that hence i don't have freedom to modify


Your tips are useful

Thank you

 

[nagovind]

@ Luke M,


I found the issue


Actually the cell contains data validation is H4


Actual data is not entered in the validation directly instead it is referred to the group of cells =$M$9:$M$11 which in turn contains the data as SC1 SC2 SC3


Please advise how to resolve this


The data is in M9 to M11 cells

 

[NARAYANK991]

Hi Govind ,


Try this :

[pre]

Range(right(Range("H4").Validation.Formula1,len(Range("H4").Validation.Formula1)-1)).Cells.Count

[/pre]
Given that the validation list is in the range M9:M11 , the above will return 3.


Narayan

 

[Hui]

Nagovind

Following on from your last post, that leads you back to my solution

=Counta($M$9:$M$11) etc

 

[NARAYANK991]

Hi Hui ,


I think that the point Govind is making is that he would like to start with the cell which has the validation , and work backwards ; looking at cell H4 , all that is available to us is the validation formula ; the range reference , which is M$9:M$11 is not available directly.


Suppose the DV list were to be extended to M9:M17 , he should be able to get 9 as the count , without having to change anything.


Narayan

 

[Luke M]

I think this falls under the idea of setting things up correctly at beginning to make things easier later. If it's possible for Data Validation range to change sizes, I think Hui's idea of having a Named Range (either dynamic range, using XL List function, etc) would be the better way. This gives the advantage of being able to be referenced then in the Data Validation, AND in a COUNTA function so that you can know how many cells/items you have.

 

[nagovind]

Dear Narayan,


Thank you for your reply


The code is not working


while running the code it is showing Compiler error: Invalid use of property


Cursor is highlighting .Count


Please guide me


@Hui


I agree with you but what Narayan says is matching with my requirement.


"Suppose the DV list were to be extended to M9:M17 , he should be able to get 9 as the count , without having to change anything."


Thank you


Govind

 

[nagovind]

Dear Narayan,


It is perfectly Working


I wrongly added your lines initially then i modified


THANKS TO Luke M


THANKS TO NARAYAN


THANKS TO Hui


Sub ValidationCount()

Dim ItemCount As Long

Dim FormHolder

FormHolder = Split(Range("H4").Validation.Formula1, ",")

ItemCount = Range(Right(Range("H4").Validation.Formula1, Len(Range("H4").Validation.Formula1) - 1)).Cells.Count

MsgBox "Number of items in validation: " & ItemCount

End Sub


[SOLVED]

 

[nagovind]

Dear Narayan,


Is there is any possibility to have a optional seek so that to combine the 2 different type of codes as below


IF error of


ItemCount = Range(Right(Range("H4").Validation.Formula1, Len(Range("H4").Validation.Formula1) - 1)).Cells.Count


then


ItemCount = UBound(FormHolder) + 1


---


Something as above so that if the validation cell contains LIST items inside the cell OR it is referring to the other cell we will get the results


Please advise


this is my optional thought by foreseeing my situation


Please


Regards

Govind

 

[nagovind]

With google search, i have modified combined Luke M and Narayan code.


Below Code Works well for BOTH type of data validation either it is inside the cell or it is referring to other cell it will count the List items


Thanks for all.


Sub ValidationCount4()

Dim ItemCount As Long

Dim FormHolder


FormHolder = Split(Range("H9").Validation.Formula1, ",")

On Error GoTo Err1

ItemCount = Range(Right(Range("H9").Validation.Formula1, Len(Range("H4").Validation.Formula1) - 1)).Cells.Count

MsgBox "Number of items in validation: " & ItemCount

Exit Sub

Err1:

FormHolder = Split(Range("H9").Validation.Formula1, ",")

On Error GoTo Err2

ItemCount = UBound(FormHolder) + 1

MsgBox "Number of items in validation: " & ItemCount

Err2:

End Sub