title: Copy non blank cell in a range and paste to only one column
body: <p>I am pretty new to vb, please help me if you can. I have a range of data，starting from N5 to R24, all cells contain formulas, but only few of them are non-blank cells.</p>
  
  <p>I want to copy only those non-blank cell to column AK，and add a string "/CA" to them。If cell in column N is ?bulk, add the number in column B first before adding "/CA",like below:</p>
  
  <pre><code>         B          N     O      P     Q     R     S           T
  5                 1PLA  2PMC                                 1PLA/CA
  6  123-01456789   1BULK                                      2PMC/CA
  7                 1AKE                                1BULK/123-01456789/CA
  8                                                             1AKE
  9
  10
  </code></pre>
  
  <p>The order in column AK doesn't matter that much, as long as it contains all non-blank cell data of range(N5:R24).</p>
  
  <p>The following code is what I've tried, but I don't know how to add string or data in column B after paste:</p>
  
  <pre><code>Sub test()
  Dim ws As Worksheet
  Set ws = Sheets("Data")
  LastRow = ws.Cells(Rows.Count, "AK").End(xlUp).Row
  For Each cell In Range("N5:R24")
  If cell.Value &lt;&gt; "" Then
  cell.Copy
  Range("AK" &amp; LastRow + 1).PasteSpecial xlPasteValues
  End If
  Next
  End Sub
  </code></pre>