title: Copy a range of cells and only select cells with data
body: <p>I'm looking for a way to copy a range of cells, but to only copy the cells that contain a value.</p>
  
  <p>In my excel sheet I have data running from A1-A18, B is empty and C1-C2. Now I would like to copy all the cells that contain a value. </p>
  
  <pre><code> With Range("A1")
       Range(.Cells(1, 1), .End(xlDown).Cells(50, 3)).Copy
   End With
  </code></pre>
  
  <p>This will copy everything from A1-C50, but I only want A1-A18 and C1-C2 to be copied seen as though these contain data. But it needs to be formed in a way that once I have data in B or my range extends, that these get copied too.</p>
  
  <pre><code>'So the range could be 5000 and it only selects the data with a value.
  With Range("A1")
  Range(.Cells(1, 1), .End(xlDown).Cells(5000, 3)).Copy
  End With
  </code></pre>
  
  <p>Thanks!</p>
  
  <hr>
  
  <p>Thanks to Jean, Current code:</p>
  
  <pre><code>Sub test()
  
  Dim i As Integer
  Sheets("Sheet1").Select
  i = 1
  
  With Range("A1")
     If .Cells(1, 1).Value = "" Then
     Else
       Range(.Cells(1, 1), .End(xlDown)).Copy Destination:=Sheets("Sheet2").Range("A" &amp; i)
       x = x + 1
     End If
  End With
  
  Sheets("Sheet1").Select
  
  x = 1
  With Range("B1")
  ' Column B may be empty. If so, xlDown will return cell C65536
  ' and whole empty column will be copied... prevent this.
      If .Cells(1, 1).Value = "" Then
         'Nothing in this column.
         'Do nothing.
      Else
         Range(.Cells(1, 1), .End(xlDown)).Copy Destination:=Sheets("Sheet2").Range("B" &amp; i)
         x = x + 1
      End If
  End With
  
  Sheets("Sheet1").Select
  
  x = 1
  With Range("C1")
      If .Cells(1, 1).Value = "" Then
      Else
          Range(.Cells(1, 1), .End(xlDown)).Copy Destination:=Sheets("Sheet2").Range("C" &amp; i)
          x = x + 1
      End If
  End With
  
  End Sub
  </code></pre>
  
  <p>A1 - A5 contains data, A6 is blanc, A7 contains data. It stops at A6 and heads over to column B, and continues in the same way.</p>