title: Select the first filtered cell then move onto the next filtered cell down
body: <p>I have an Excel spreadsheet that has contact details, for example:</p>
  
  <pre><code>    A                 B            C            D                    E
  1   Select who you would to like to email:      * Drop down list *
  2   Name:            Company:      Role:        Email Address1:      Email Address2:
  3   Michael Jackson  Jackson 5     Singer       MJ@J5.com            Michael@J5.com
  4   Brian May        Queen         Guitarist    BM@Queen.com         Brian@Queen.com
  5   Kurt Cobain      Nirvana       Singer       KC@Nirvana.com       Kurt@Nirvana.com
  6   Freddie Mercury  Queen         Singer       FM@Queen.co.uk       Freddie@Queen.com
  7   Pat Smear        Nirvana       Guitarist    PS@Foo.com           Pat@Foo.com
  </code></pre>
  
  <p>A user selects an email address using the drop down list in <code>D1</code> then runs a macro that gets the email addreses in that column.</p>
  
  <p>The problem is when a user applies a filter, say all guitarists, it will select the first filtered row (<code>C4</code>) and then go to the next row rather than the next filtered row, so it would go to <code>C5</code>.</p>
  
  <p>This is an adaption of the code:</p>
  
  <pre><code>Sub SendEmail()
  
  Dim objOutlook As Object
  Dim objMail As Object
  Dim RowsCount As Integer
  Dim Index As Integer
  Dim Recipients As String
  Dim Category As String
  Dim CellReference As Integer
  
  Set objOutlook = CreateObject("Outlook.Application")
  Set objMail = objOutlook.CreateItem(0)
  
  RowsCount = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  
  Category = Range("D1")
  Dim RowLimit As String
  If Category = "Email Address1" Then
      CellReference = 4
  ElseIf Category = "Email Address2" Then
      CellReference = 5
  End If
  
  Index = 0
  While Index &lt; RowsCount
      Set EmailAdrs = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, CellReference).Offset(0 + Index, 0)
      Recipients = Recipients &amp; EmailAdrs.Value &amp; ";"
      Index = Index + 1
  Wend
  
   With objMail
      .To = Recipients
      .Subject = "This is the subject"
      .Display
  End With
  
  Set objOutlook = Nothing
  Set objMail = Nothing
  
  End Sub
  </code></pre>
  
  <p>I tried looping through rows that are hidden:</p>
  
  <pre><code>While Index &lt; RowsCount
     Do While Rows(ActiveCell.Row).Hidden = True
         'ActiveCell.Offset(1).Select
         Set EmailAdrs = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, CellReference).Offset(0 + Index, 0)
          Recipients = Recipients &amp; EmailAdrs.Value &amp; ";"
          Index = Index + 1
          ActiveCell = ActiveCell.Offset(0 + Index, 0).Select
      Loop
  Wend
  </code></pre>
  
  <p>I tried going through only cells that are visible.</p>
  
  <p>I tried ideas from <a href="https://stackoverflow.com/questions/31593414/vba-go-to-the-next-filtered-cell">VBA Go to the next filtered cell</a>:</p>
  
  <pre><code>If ActiveSheet.FilterMode = True Then
      With ActiveSheet.AutoFilter.Range
          For Each a In .Offset(1).Resize(.Rows.Count).SpecialCells(xlCellTypeVisible).Areas
              Recipients = Recipients &amp; a(1, CellReference) &amp; ";"
          Next
      End With
      MsgBox Replace(Recipients, ";;", vbNullString)
  End If
  </code></pre>
  
  <p>And:</p>
  
  <pre><code>Dim Rng As Range
  If Category = Range("S2") Then
      CellReference = 10
      'Set your range
      Set Rng = Range("A1:B2")
  ElseIf Category = Range("S3") Then
      CellReference = 14
      'Set your range
      Set Rng = Range("C1:D2")
  ElseIf Category = Range("S4") Then
      CellReference = 18
      'Set your range
      Set Rng = Range("F1:G2")
  ElseIf Category = Range("S5") Then
      CellReference = 16
      'Set your range
      Set Rng = Range("H1:J2")
  End If
  
  For Each mCell In ThisWorkbook.Sheets("YourSheetName").Range(Rng).SpecialCells(xlCellTypeVisible)
      'Get cell address
      mAddr = mCell.Address
      'Get the address of the cell on the column you need
      NewCellAddr = mCell.Offset(0, ColumnsOffset).Address
      'Do everything you need
  Next mCell
  </code></pre>