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

 

[Akuini]

I think it's typo in "WithThisWorkbook.Sheets ("Data")" . Try this one:

With ThisWorkbook.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

 

[Patriot2879]

Hi I did that I get a compile error on this line now

ComboBox2.List = .Range("A2:R" & .Range

 

[Akuini]

Hm, try this:

With ThisWorkbook.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

 

[Patriot2879]

Hi that works great thank you for your help on this
Please can you advise what the application transpose does please?

 

[Akuini]

Hm, I'm not good at explaining things, you can can just google it.
Basically, in this case, Application.Transpose is used to transform data in column to row.
Range B1:R1 is multi column whilst you need the value in a single column (multi row) combobox. So you need to transform it to row .

 

[Patriot2879]

Ok thank you, I appreciate your help and support.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.