title: VBA - Filtered list to ComboBox with multiple columns
body: <p>I've got an Excel sheet that takes a range filtered on multiple columns dependant on buttons and populates a combo-box.</p>
  
  <p>The idea is I'm building a product picker with multiple categories (can be part of multiple categories).</p>
  
  <p>VBA is not my area and the solution that I've got, after a couple of days of searching, is very inelegant.</p>
  
  <p>Problems I'm facing:</p>
  
  <ul>
  <li>General lack of knowledge handling types in VBA e.g range, arrays, etc.</li>
  <li>SpecialCells(xlCellTypeVisible) -> not behaving as I would have thought
  -- Consequently having to have multiple sorts to get data into combo box</li>
  </ul>
  
  <p>PseudoCode:</p>
  
  <ol>
  <li>Activate Worksheet and copy named range to a oldRange</li>
  <li>Select -> This way I can see what is happening. </li>
  <li>Disable autofilter if already set 'to clear</li>
  <li>Sort and filter with autofilter</li>
  <li>select -> So I can see what's happening</li>
  <li>set to newRange using oldRange.offset(1,0).SpecialCells(xlCellTypeVisible) -> get rid of headers and filtered rows</li>
  <li>newArray = newRange</li>
  <li>combobox.list = newArray</li>
  </ol>
  
  <p>Spreadsheet:
  The data is comming from multiple tabs (selected by RadioButtons) that are formatted differently
  Require 11 columns to be displayed in the comboBox</p>
  
  <p>Question:
  Can anyone answer why the newRange=oldRange.SpecialCells(xlCellTypeVisible) doesn't transfer to the combobox.list properly?
  I can see in the excel window behind the form that the autofilter is working and then selecting the correct data.
  The problem I face is that it populates the ComboBox list until the first hidden/filtered value and then stops. This means I can get it to work by sorting first but I'm not confident that I'll be able to scale this up with multiple sorts &amp; multiple filters that avoid any hidden values between good values (causing the comboBox list to be incomplete)...</p>
  
  <p>Though this might work, I feel like I'm making a meal of it, is there a simpler way to get these values in to the combo Box.</p>
  
  <p>Thanks In Advance</p>