Make print area dynamic & replace a SUMPRODUCT with code!

Is it noticeably quicker? You should probably add screenupdating off/on as well:

Public Function Sumproduct_Replacement(a as range, b as range, c as range, d as range, e as range)
application.screenupdating = false
dim rng, total,a1
set rng = c.find(d)
do until rng is nothing  
  if Sheets("Data").cells(rng.row,a.column) < b then a1 = 1 else a1 = 0
     total = total + (a1*cells(rng.row,c.column))
  end if
  set rng = Sheets("Data").cells.findnext(rng)
loop
next
application.screenupdating = true
end function