title: Need a "Simple" excel macro to find bottom cell in a column, create a range and copy it
body: <p>I have a spreadsheet I'm using to compile text that changes all the time. </p>
  
  <p>In column AD, Row 4(AD4) I put the contents of text, and it can have data going 1000 to 4000 rows down. It changes every time, so there is no static range name. I need a macro that</p>
  
  <ul>
  <li>finds the final piece of data in that column, </li>
  <li>then automatically "drags a box" from that spot two columns to the left (AB4)</li>
  <li>and copies it... (A 3000 row piece of text would be AB4:AD3004) (Macro stops there, with text to be copied highlighted)</li>
  </ul>
  
  <p>The current version finds the bottom cell correctly, but if I run the macro a 2nd time, with new data, it keeps trying to copy the same range. (I used the Formula Define.Name method, to name the cell, and then selected AB4:LastRow) but it is ALWAYS 3160 whether data goes to row 4000 or not.....</p>
  
  <pre><code>Sub Last_row()
  Cells(Application.Rows.Count, 30).End(xlUp).Select
  ' following lines of code are useless
  Range("AB4:AD3160").Select
  Range("AD3160").Activate
  Selection.Copy
  End Sub
  </code></pre>