Sumproduct with dynamic range

[Xcels]

'Duplicate Check Column V
Range("V1").Select
ActiveCell.FormulaR1C1 = "Duplicate Check"
Range("V2").Select
ActiveCell.Formula2R1C1 = _
"=SUMPRODUCT((RC15<=R2C16.End(x1down))*(RC16>=R2C15.End(x1down))*(RC7=R2C7.End(x1down))"
Range("V2").Select
Selection.AutoFill Destination:=Range("V2.End(x1down)")

The formula is as follows:
=SUMPRODUCT(($O2<=$P$2:$P$10000)*($P2>=$O$2:$O$10000)*($G2=$G$2:$G$10000))

I put a range up to 10000 rows but in theory the number of rows differ with each worksheet. So, it would be ideal to capture till last row.

Any help would be much appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

   Dim Lr As Long
   'Duplicate Check Column V
   Range("V1").Value = "Duplicate Check"
   Lr = Range("O" & Rows.Count).End(xlUp).Row
   Range("V2:V" & Lr).Formula2R1C1 = _
      "=SUMPRODUCT((RC15<=R2C16:r" & Lr & "c16)*(RC16>=R2C15:r" & Lr & "c15)*(RC7=R2C7:r" & Lr & "c7))"

 

[Xcels]

Hi,

Thank you so much. It works exactly as I wanted. I appreciate the help!

 

[Fluff]

You're welcome & thanks for the feedback.