Transpose to Array

[MM91]

I want to make a macro that opens a workbook ("StandardParts") and then opens an excel userform(SearchUF) that has a textbox (userinput) then opens a worksheet ("StandardParts") and searches a worksheet ("List") Row C a for a match. Hi I am trying to write a code to get the values from row a and create a combo box list. I need it to search the column and then populate the combobox (combobox1) without duplicates. so the drop down list for this example would be "name1" "name2"' "name3". Any help is appreciated I am stuck thanks


Dim ws as worksheet
Dim List as varient


Sub Test()

Dim LastRow As Long

lastrow = Cells(Rows.count,1).Endxl(up).row

ws = activesheet

For i = 1 to Lastrow
if lastrow(i) <> cells(i,1) then
set List(i)
next i

End Sub

 

[Anthony47]

Hi I am trying to write a code to get the values from row a and create a combo box list. I need it to search the column and then populate the combobox (combobox1) without duplicates. so the drop down list for this example would be "name1" "name2"' "name3"

I seem that your question is the one I quoted (above) and we can ignore the first part of your message.
Thus I suggest the following code:

Dim LastRow As Long, I As Long
Dim CBList(), CBInd As Long, dataCol As String
'
dataCol = "A"                                      '<<< The column to look after
LastRow = Cells(Rows.Count, dataCol).End(xlUp).Row
ReDim CBList(1 To LastRow)
For I = 1 To LastRow
    If Application.WorksheetFunction.CountIf(Cells(1, dataCol).Resize(I, 1), Cells(I, dataCol).Value) = 1 Then
        CBInd = CBInd + 1
        CBList(CBInd) = Cells(I, dataCol)
    End If
Next I
ReDim Preserve CBList(1 To CBInd)
'Now you are ready to dump CBList to your Combobox
'for example:
UserForm1.ComboBox1.List = CBList                 '<<< Adapt

Try...

 

[MM91]

Perfect thank you that makes sense!