Include Blank Value In Validation List

[karthikeyan]

hi, i have two sheets in workbook named as shtbiz,shtbiz1.
so column A of my shtbiz1 sheet i have 100 datas.the by using following code i can fill the datas of shtbiz1 in column A of shtbiz.
code:

Dim strValItems
 strValItems = ""
 Dim rngValList As Range, rngValCell As Range
    Set rngValList = ShtBiz1.Range("A6", Range("A10000").End(xlUp))
    With ShtBiz.Range("A6:A10000")
        .Validation.Delete
        For Each rngValCell In rngValList
               If Len(strValItems) > 0 Then strValItems = strValItems & ","
                strValItems = strValItems & rngValCell.Value
           
        Next
        .Validation.Add xlValidateList, , , strValItems
      End With

so my problem is i need to add blank value in the top of the list .ie first value should be blank then other values which read from shtbiz1 has to be followed.so please help me to include one blank value at the to pof my validation list please

[westconn1]

For Each rngValCell In rngValList
               if rngvalcell.address = rangevallist("A1").address then ' allow blank
               If Len(strValItems) > 0 Then strValItems = strValItems & ","
                strValItems = strValItems & rngValCell.Value
           
        Next

[karthikeyan]

dear friend,
will your code allow blank at the top of the validation list?

[westconn1]

that is up to you, i just show you how to find if it is the first cell in the range, than you can put what you want

otherwise try like

For Each rngValCell In rngValList
               '''If Len(strValItems) > 0 Then strValItems = strValItems & ","
                strValItems = strValItems & rngValCell.Value & ","
        Next

this will make a comma the first character if the first cell is empty
trim last character after loop to remove trailing comma

[karthikeyan]

Dear Friend, i have tried as u said,but it show error message.i have attached sample excel file which i have tried.please look at the excel file.please help me to include blank at the top of my validationlist where ever comming in this excel file upto A1 to A10 my validtion list has to come.so in my validation list the very first value shoud be blank followed by sheet2 values shoud cme.please hellp me

[westconn1]

try this

Private Sub CommandButton2_Click()
 
 Dim rngValList As Range, rngValCell As Range
    Dim strValItems As String
    Set rngValList = Sheet2.Range("A1", Sheet2.Range("A10").End(xlUp))
    With Sheet1.Range("A1:A10")
        .Validation.Delete
        For Each rngValCell In rngValList
        If Not rngValCell.Address = rngValList.Range("A1").Address Then
                strValItems = strValItems & ","
                strValItems = strValItems & rngValCell.Value
        End If
         Next
        .Validation.Add xlValidateList, , , strValItems
    End With
End Sub

or this will be slightly quicker as id does not need to evaluate the address for every cell in the range

Private Sub CommandButton2_Click()
 
 Dim rngValList As Range, rngValCell As Range
    Dim strValItems As String
    Set rngValList = Sheet2.Range("A1", Sheet2.Range("A10").End(xlUp))
    strValItems = ""
    With Sheet1.Range("A1:A10")
        .Validation.Delete
        For Each rngValCell In rngValList.Offset(1, 0)
                strValItems = strValItems & ","
                strValItems = strValItems & rngValCell.Value
         Next
        .Validation.Add xlValidateList, , , strValItems
    End With
End Sub

[karthikeyan]

Dear westconn1,
thanks for helping me.
i have tried your code. it is not giving my requiremnet properly friend.
it is giving error .i have attache the error and my workbook also please see the error and help me to achieve my need please friend

[anhn]

1. You don't need to use code to set Validation List for the range Sheet1!A6:A10000 even if the list is not in the same sheet and even you want to include a blank item.

Case 1:
Suppose that your Validation List is also in Sheet1 in column K, starting from row 6 with unknown ending row.
Leave cell K6 blank and fill all other cells below with your list.
Select range A6:A10000 (or whatever you want), with Data > Validation > Setting dialog box, set:
Allow: List
Source: =$K$6:$K$65536

Case 2:
Now, it's harder when your Validation List is in Sheet2, the Data Validation Source does not allow to enter the list range from another sheet with similar format as above, but you can use range Name (even that range belongs to another sheet), eg.
Source: =list_mbe
Now you just need to redefine your "list_mbe" range Name, instead of the whole column A in Sheet2, you can set that Name refer to Sheet2!A6:A10000 or event Sheet2!A6:A65536, don't worry about the blank cells at the bottom of the list, Excel will ignore them.
Again, leave cell A6 in Sheet2 blank.

2. For your reference, below is the code if you want to set it by code:
* Change ShtBiz and Sheet2 to match with your workbook
* You may already know, "ShtBiz" is the first sheet CodeName but "Sheet2" is the second sheet Name.

Private Sub CommandButton2_Click()
   '-- Make sure cell "A6" in Sheet2 is blank
   ThisWorkbook.Names.Add Name:="list_mbe", RefersToR1C1:="=Sheet2!R6C1:R65536C1"
   With ShtBiz.Range("A1:A10000").Validation
      .Delete
      .Add xlValidateList, , , "=list_mbe"
   End With
End Sub

[karthikeyan]

dear friend,
as per your sugestion i have tried but it show s the following error :

a run time error ‘-2147417848 (80010108)’ Method ‘Add’ of object ‘Validation’ failed
so please help me avoid this error please