Array CSE Formula in Macro

[Cosmos75]

I am trying to add an Array Formula in my macro but it isn’t working. Here's the formula:

{=MAX(IF(G2:G207="Y",I2:I207))}

I need the ranges for the G and I Column to be done with using the length of Column I minus the Heading so it Column I has 35 values, I need the ranges to be G2:G36 and I2:I36.

Range("I2").Select
Range(Selection, Range("I65536").End(xlUp)).Select
RowCounter = Selection.Count
Range("L7").Select
Selection.FormulaArray = "=MAX(IF(G2:" & Range("G" & RowCounter + 1).Address & "=Y,(I2:" & Range("I65536").End(xlUp).Address & "))"

Can anybody help?

 

[BabyTiger]

I believe those rows after row 71 were used at some stage, although they are cleared now, Excel still thinks it is being used, alternatively, if your column i is continuous data column (ie, no empty cells in between i2 and i71), then you might want to use the following line of code:

range(i2).end(xldown).row

instead of "xllastcell".

If all the rows after 71, are truly empty, then Excel will reset the LastCell range when you save the workbook.