title: Excel vba select non blank cells in column A and create array offset from B
body: <p>I have a list of values in column A2:A10 some are empty
  What I would like to do is create a comma separated array of the adjacent values in column B.</p>
  
  <pre><code>Therefore if A3 = X and A6 = X and A9 = X
  The result should be what is in Column B i.e. B3 = Y and B6 = Y and B9 = Y
  These need to be presented in a comma separated array i.e. y,y,y
  </code></pre>
  
  <blockquote>
    <p>Note: the x and y values are different numbers, not actual X or Y</p>
  </blockquote>
  
  <p>I can create the array offset, using the following but it selects all the values in column B, whereas I only want the adjacent ones from column A</p>
  
  <pre><code>Dim arr
  Dim LR As Long
  
      LR = Range("A" &amp; Rows.Count).End(xlUp).Row
      On Error Resume Next    'if only 1 row
      arr = Join(Application.Transpose(ThisWorkbook.Sheets("ID").Range("A2:A" &amp; LR).Offset(0, 1).Value), ",")
  
  MsgBox arr
  </code></pre>