title: Excel: Working around sorting tables by date, with empty rows in the range
body: <p>I am working on a model that requires me to sort data in a range by date, before copying the data into a different template on another sheet. Every row has a formula that pulls data from Bloomberg, so even if the cell looks empty, excel recognizes that there is content in the cell. Sorting the date column as it is does not work, as excel wont recognize the data that Bloomberg pulls as a date, so it would be sorted from A to Z, which scrambles the dates instead.</p>
  
  <p>To work around this, I inserted and adjacent column with the formula (using cell A1 as an example) "=(A1+0)" that then allows the column to be sorted from new to old. The problem here is that if cell A1 does not display a date (in other words, it appears empty yet the cell contains a formula that leaves the cell looking empty if no date is pulled) it returns a #VALUE! error and sorting new to old would put the errors at the top (Thank you Excel for this fantastic feature, btw). </p>
  
  <p>To work around this new issue, I replaced the above formula in the cell with "=IFERROR(A1+0,1)" which gives me the date 1/1/1900. Fine, now the data is sorted in the manner I want it, but I have a bunch of ancient dates that just make my end product look ugly.</p>
  
  <p>I have two questions, first; how can I use VBA to delete the data in the cells where the date equals 1 (which shows the date 1/1/1900), and only those cells? Or, alternatively, only copying the rows above the cell that contains 1/1/1900.  This is a relatively small amount of cells that would be affected by this, 40 at most.</p>
  
  <p>Second; is there a different way of sorting the data using VBA that I am missing, that might be more efficient?</p>