title: VBA Select Filtered Cells
body: <p>I have a UserForm in a sheet.
  In this form I have 6 combobox.</p>
  
  <p>This combobox are populated from a sheet with 6 columns, each column goes to a combobox.
  After each combobox is selected, I make a filter at this sheet and repopulate the next one.</p>
  
  <p>I'll give you an example to try to make it more clear.</p>
  
  <p>I have a sheet with 6 columns:<br>
  Continent | Country | State | City | Street | Name of the building</p>
  
  <p>This sheet have ALL the possible combinations for all this itens.
  For example:
  For each building in a street I have a row with all the same 5 first items and the last one changes.</p>
  
  <p>When the user opens the form I populate the first combobox with the first column of the sheet (I do a routine to get unique items).
  When the user changes the first combobox, I apply a filter to the sheet in the first column and then I populate the second combobox with the filtered sheet.</p>
  
  <p>My problem is how to get the filtered range.
  I'm doing this:</p>
  
  <p>lastRow = Sheets("SIP").Range("A65536").End(xlUp).Row<br>
  lFiltered = Sheets("SIP").Range("A2:F" &amp; lastRow).SpecialCells(xlCellTypeVisible).Cells</p>
  
  <p>It works fine. But when I apply a filter and it hides, for exemple, only the row 10, the lFiltered variable will return only until row 9.
  It breaks on the first hidden row and does not return any row after that.</p>
  
  <p>The solution I came up with is to do a foreach with every row and check if its visible or not, but the code gets really, really slow. It takes up to 10 seconds to populate each combobox.</p>
  
  <p>Anyone have any idea how can I work around this issue?</p>
  
  <p>Thank you very much.</p>
  
  <p>-- edit --</p>
  
  <p>Here is the important part of the code</p>
  
  <pre><code>Dim listaDados As New Collection
  Dim comboList() As String
  Dim currentValue As String
  Dim splitValue() As String
  Dim i As Integer
  Dim l As Variant
  Dim lFiltered As Variant
  Dim lastRow As Integer
  
  'Here I found the last row from the table
  lastRow = Sheets("SIP").Range("A65536").End(xlUp).Row
  'I do this because when the filter filters everything, lastRow = 1 so I got an erros on lFiltered range, it becames Range("A2:F1")
  If lastRow &lt; 2 Then
      lastRow = 2
  End If
  'Here I get an array with all the visible rows from the table -&gt; lFiltered(row, column) = value
  lFiltered = Sheets("SIP").Range("A2:F" &amp; lastRow).SpecialCells(xlCellTypeVisible).Cells
  'I have duplicated entries, so I insert everything in a Collection, so it only allows me to have one of each value
  on error resume next
  For i = 1 To UBound(lFiltered)
      currentValue = Trim(lFiltered(i, column))
      If currentValue &lt;&gt; 0 Then
          If currentValue &lt;&gt; "" Then
              'Cammel case the string
              currentValue = UCase(Left(currentValue, 1)) &amp; LCase(Mid(currentValue, 2))
              'Upper case the content in between "( )"
              splitValue = Split(currentValue, "(", 2)
              currentValue = splitValue(0) &amp; "(" &amp; UCase(splitValue(1))
              'Insert new item to the collection
              listaDados.Add Item:=currentValue, Key:=currentValue
          End If
      End If
  Next i
  i = 1
  'Here I copy the collection to an array
  ReDim Preserve comboList(0)
  comboList(0) = ""
  For Each l In listaDados
      ReDim Preserve comboList(i)
      comboList(i) = l
      i = i + 1
  Next l
  
  'Here I assign that array to the combobox
  formPerda.Controls("cGrupo" &amp; column).List = comboList
  </code></pre>
  
  <p>--- edit --- </p>
  
  <p>Here is how I managed the code to work the way I want.</p>
  
  <pre><code>'Get the last row the filter shows
  lastRow = Sheets("SIP").Range("A65536").End(xlUp).Row
  'To avoid to get the header of the table
  If lastRow &lt; 2 Then
      lastRow = 2
  End If
  'Get the multiple range showed by the autofilter
  Set lFilteredAux = Sheets("SIP").Range("A2:F" &amp; lastRow).Cells.SpecialCells(xlCellTypeVisible)
  
  'Check if there is more than 1 no contiguous areas
  If Sheets("SIP").Range(lFilteredAux.Address).Areas.Count &gt; 1 Then
      'If Yes, do a loop through the areas
      For i = 1 To Sheets("SIP").Range(lFilteredAux.Address).Areas.Count
          'And add it to the lFiltered array
          ReDim Preserve lFiltered(i - 1)
          lFiltered(i - 1) = Sheets("SIP").Range(lFilteredAux.Address).Areas(i)
      Next i
  Else
      'If there is only one area, it goes the old way
      ReDim lFiltered(0)
      lFiltered(0) = Sheets("SIP").Range(lFilteredAux.Address)
  End If
  </code></pre>
  
  <p>Now I have the lFiltered array a little different than the way I was using, but I adapted my foreach to work like this:</p>
  
  <pre><code>For i = 0 To UBound(lFiltered)
          For j = 1 To UBound(lFiltered(i))
              currentValue = Trim(lFiltered(i)(j, columnNumber))
          next j
  next i
  </code></pre>
  
  <p>Thanks a lot! =D</p>