title: Convert Column Headers to List Entries of a ComboBox in VBA/Excel
body: <p>I have a combobox that is on a userform that I would like to populate with a list of values. </p>
  
  <p>The  values are in the middle of a series of columns and can be thought of as column headers as each is in its own column. The list of columns will <em>expand</em> over time and should be updated each time the user form is initialized. I have been able to create a list from a single column and I can also create a list from multiple columns but when I do, the values remain in a column orientation and I can't get them to <em>transpose</em> into a list of rows. The data in the columns looks like this:</p>
  
  <pre><code>|--|--A--+--B--+--C--+--D--+ ... +
  |--|-----+-----+-----+-----+
  |1 |     |     |     |     |
  |--|-----+-----+-----+-----+
  |2 |  &lt;&lt; other data here &gt;&gt;|        
  |--|     +-----+-----+-----+
  |3 |  "  |  a  |  b  |  c  | ...    &lt;~~ row 3 data (needed as 2nd element in combobox)
  |--|     +-----+-----+-----+
  |4 |  "  |  d  |  e  |  f  | ...    &lt;~~ row 4 data (needed as 1st element in combobox)
  |--|     +-----+-----+-----+
  |5 |  "  |  1  |  2  |  3  |
  |--|     +-----+-----+-----+
  |6 |  "  |  4  |  5  |  6  |
  |--|-----+-----+-----+-----+
  |7 |  "  |  7  |  8  |  9  |
  |--|-----+-----+-----+-----+
  </code></pre>
  
  <p>I want the combobox list entries to appear like this:</p>
  
  <pre><code>d   a
  e   b
  f   c
  </code></pre>
  
  <p>This userform is initialized by another userform which selects from several options and then activates the correct worksheet from several worksheets that have similar information. When the second userform initializes, it should populate the combobox with the data in the example above. I created a list of values from a single column using:</p>
  
  <pre><code>Private Sub UserForm_Initialize()
  
      'ReferenceCombo.ColumnCount = 2
      'Range("B4", Range("B" &amp; Rows.Count).End(xlUp)).Name = "Dynamic"
      'Me.ReferenceCombo.RowSource = "Dynamic"
  
  End Sub
  </code></pre>
  
  <p>I can also get a list of all values in a row using:</p>
  
  <pre><code>Dim sht As Worksheet
  Set sht = ActiveSheet
  
      ReferenceCombo.ColumnCount = 2
      sht.Range(Sheet7.Cells(4, 2), Sheet7.Cells(4, Columns.Count).End(xlToLeft)).Name = "Dynamic"
      sht.Range(Sheet7.Cells(4, 2), Sheet7.Cells(4, Columns.Count).End(xlToLeft)).Select
      Me.ReferenceCombo.RowSource = "Dynamic"
  </code></pre>
  
  <p>but this only selects one of the two rows I want and doesn't transpose the list of values into the correct format.</p>
  
  <p>I've tried Information from <a href="https://stackoverflow.com/questions/4200712/dynamically-set-listfillrange-in-excel-combobox-using-vba">here</a> but this does not seem to apply to userforms. Information <a href="https://stackoverflow.com/questions/12064731/excel-vba-select-a-dynamic-cell-range">here</a> was helpful in selecting the row correctly. <a href="https://www.excelforum.com/excel-programming-vba-macros/331680-filling-combobox-with-variable-length-list.html" rel="nofollow noreferrer">This</a> was helpful in selecting a dynamic field. <a href="https://www.ozgrid.com/forum/forum/help-forums/excel-general/42517-transpose-named-range-for-combo-box-list" rel="nofollow noreferrer">Here</a> I found information on transposing a list but I'm not sure I understood it completely. <a href="https://social.msdn.microsoft.com/Forums/office/en-US/8efc81ee-415d-4c59-a4f0-941713ab3567/setting-combobox-listfillrange-from-the-properties-menu?forum=exceldev" rel="nofollow noreferrer">This</a> relates to a listfill range but I'm not sure this applies to comboboxes on userforms either. <a href="https://bettersolutions.com/vba/arrays/transposing.htm" rel="nofollow noreferrer">This</a> relates to inserting a transpose function but it didn't work for me. </p>
  
  <p>Any help would be greatly appreciated.</p>