VBA Code to Extract Data from a List and Making a Validation

[OilEconomist]

Hello and thanks in advance for your assistance. I would like to make a data validation on a particular sheet by extracting entries from a column on another sheet. I would also like to eliminate any duplicate values from the list.

My issue is how to get the values to appear in the formula bar. I would like to not use name ranges. I am having issues with the following line of code.

    Sheets("Plan.Loader").Range("A7").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=RngDatVal

So basically is there some way for it to automatically extract and input the values into the formula to get this:

    Sheets("Plan.Loader").Range("A7").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Dog,Cat,Bird,Mouse"

The code is as follows. Also, how much does the code change if it needs to eliminate any duplicates values from the range. In terms of ensuring that none of the values repeat themselves in the data validation?

Sub DatVal()

'Dimensioning
   Dim ShtNm As String
   Dim LastRow As Long
   Dim RngDatVal As Range
  
  
Sheets("Plan.Loader").Activate

'Code
    ShtNm = "Plan.2022.02.28"

    With Sheets(ShtNm)
       Set RngDatVal = .Range("C9:C17")
    End With

    Sheets("Plan.Loader").Range("A7").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:=RngDatVal


End Sub

 

[mumps]

Glad we could help.

 

[OilEconomist]

Thanks @Fluff as this also works, but only if Sheets("Plan.2022.02.28") is active. When I am on any other sheet, it gives me the error "Run-time error '1004': Application-defined or object-defined error" for the following line of code:

or Each Cl In .Range("C9", Range("C" & Rows.Count).End(xlUp))

The code @mumps posted also eliminates the spaces/blank entries.

@Fluff Ah. I should have been able to catch that. After the adjustment it also works. Thanks again for your continued help.

 

[OilEconomist]

Glad we could help.

@mumps Your assistance is always appreciated. I am continuing to learn from @Fluff and you!

 

[Fluff]

Glad to help & thanks for the feedback.