title: how to select column till last non empty cell
body: <p>I am creating vba macro to copy a column having set of values.
  but in between some rows are blank in that column.how to  code it dynamically so that it will select till last non empty cell.</p>
  
  <p>for example in cell A
  if A1 to A20 have values 
  A21 is blank
  A22 to A35  have values
  A36,A37,A38 is blank
  A39 to A45 hav values</p>
  
  <p>Now i need to dynamically select values from A1 to A45 and paste it in B.
  Also values in A Come through formula.I want to carry that formula and paste it as formula</p>
  
  <p>how do i do it?</p>