Naming Range for Combobox specifically

[Patriot2879]

Hi please can you help me,
I have the code below where combobox2 is working fine, butwith combobox1 I want the range to be B1, C1, D1, E1, F1, G1, H1, J1, K1, L1,M1, N1, O1, P1, Q1, R1, please can you help?

With ThisWorkbook.Sheets("Data")
    ComboBox1.RowSource = ""
    ComboBox1.List = .Range("B1:R" & .Range("B" & Rows.Count).End(xlUp).Row).Value
    ComboBox2.RowSource = ""
    ComboBox2.List = .Range("A2:R" & .Range("A" & Rows.Count).End(xlUp).Row).Value

End With

 

[Patriot2879]

Hi I have tried changing it to the below as well but this didn't work. hope you can help.

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT=Calibri][FONT=Calibri]WithThisWorkbook.Sheets("Data")
    ComboBox1.RowSource = ""
    ComboBox1.List = .Range("B1, C1, D1, E1, F1, G1, H1,J1, K1, L1,M1, N1, O1, P1, Q1, R1").Value
    ComboBox2.RowSource = ""
    ComboBox2.List = .Range("A2:R" &.Range("A" & Rows.Count).End(xlUp).Row).Value

End With[/FONT][/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]

 

[My Aswer Is This]

Are both of these Multicolumn ComboBoxes?
And what type ComboBox
Form
Activex

Or Userform

 

[Patriot2879]

What do you mean? each combobox has a list for example combobox1 has a lit of 18 items and combobox has a list f 18 items, combobox2 to was a1 to a18 an and combobox1 is b2 to r18, in sheet called 'Data'

they are both in a userform

 

[Akuini]

ComboBox1.List = .Range("B1, C1, D1, E1, F1, G1, H1,J1, K1, L1,M1, N1, O1, P1, Q1, R1").Value

Do you mean range B1:R1 without I1?

And is it a single column or multiple column combobox?

 

[Patriot2879]

Hi yes B1:R1, I have data in I1 as well.

I think with your second question it is singlecolumn, I only have dates in B1:R1 which I want to go into combobox1

 

[Akuini]

Hi yes B1:R1, I have data in I1 as well.

Ok, so range B1:R1 (including I1).
1 column combobox means range B1:R1 becomes the rows ( not columns) of the list in the combobox.
Try this:

WithThisWorkbook.Sheets ("Data")
ComboBox1.RowSource = ""
ComboBox1.List = Application.Transpose(.Range("B1:R1").Value)
ComboBox2.RowSource = ""
ComboBox2.List = .Range("A2:R" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With

 

[My Aswer Is This]

Edit

 

[Patriot2879]

Hi thank you for the code but I get a compile error on the snippet below.

Transpose(.Range

 

[My Aswer Is This]

Change it to this:

ComboBox1.RowSource = ""
ComboBox1.List = Application.Transpose(Range("B1:R1").Value)