title: Copying a variable range based on nonempty text fields
body: <p>In worksheet1 ('add') I have a table ranging from <code>A4</code> to <code>Z14</code>. It is used to show a voyage of a ship with the different ports and automatically calculates times and dates. I want to copy this table (this specific voyage) to another worksheet2 ('schedule') with the full itinerary of the vessel. To do this I copy the range to the clipboard using VBA. (I run some other macros with it but explaining becomes somewhat elaborate.) </p>
  
  <p>My problem: I want to copy only the range that contains portcalls. Ports are entered in column C so range <code>C4</code> to <code>C14</code>. So if there are only three ports in the voyage, <code>C4</code> to <code>C6</code> contains port names and <code>C7</code> to <code>C14</code> are empty or 0. The copied range should then be <code>A4:Z6</code>. If there are five ports the range should be <code>A4:Z8</code>. </p>
  
  <p>Since for some cells the formulas need to be copied, and for some only the values (that come from a general input sheet), I first inserted the complete range and then copied/pasted values of the ranges over it to break the link with the general input sheet.</p>
  
  <pre><code>Dim myC As Range
  Set myC = ActiveCell
  Application.CutCopyMode = False
            'insert 
  Sheets("Add").Select
  Rows("5:14").Select
  Selection.COPY
  Sheets("Schedule").Select
  myC.Select
  ActiveCell.Offset(1, 0).Select
  Selection.Insert Shift:=xlDown
  Application.CutCopyMode = False
  
  'paste values voy#/ports            
  Sheets("Add").Select
  Application.CutCopyMode = False
  Range("B5:C14").Select
  Selection.COPY
  Sheets("Schedule").Select
  myC.Select
  ActiveCell.Offset(1, 1).Select
  Selection.PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Application.CutCopyMode = False
  </code></pre>
  
  <p>--> repeats pastevalues for ranges <code>E5:J14</code>, <code>M5:R14</code>, <code>T5:T14</code>, <code>AB5:AG14</code></p>
  
  <p>the row number of the ranges should depend on the last row with a value in it within the range <code>C4:C14</code></p>
  
  <p>I have looked at many of the different problems/answers but all are slightly different than mine.</p>