title: Excel macro to replace missing data with average
body: <p>I have a range of data, from a vba prompt held in a type range variable. I am trying to then go through this range, and for each empty cell, replace it with the average of it's column.</p>
  
  <p>I can replace the empty cells fine, but it's getting the average of each column I am strugging with. Is there an easy way? </p>
  
  <p>I prefer not to do a loop for each column, a loop for each row and then calculate it using total/numberOfNonEmptyCells as this takes a fair bit of time ( And i'm evidently doing something wrong here as i'm getting the wrong value )... but i'm sure it's not the only or correct way to do it.</p>
  
  <p>Is there any kind of way of saying:</p>
  
  <blockquote>
    <p>For Each Column<br>
    Get column i from the range<br>
    Get the average of that column.<br>
    Put it in an array.  </p>
  </blockquote>
  
  <p>Then have a loop going through replacing the empty cells with that value?</p>
  
  <p>OR, even better a way of just putting in a formula into the empty cells saying<br>
  <code>= AVERAGE(Column from Range)</code><br>
  I tried this using <code>AVERAGE(Range.Columns,1)</code> and it didn't work, as it is of type Column I believe which average won't work with.</p>
  
  <p>The easiest way to do this I will mark as the answer. It needs to use a macro though rather than just being manually putting the formula in, as it's for a lot of data on a lot of sheets.</p>
  
  <p>Thanks.</p>