I need help in converting the below formulas in VBA however I am unable to do so.

I would want to define the range in such a way that it has only the last row.

Presently I am using the INDIRECT FUNCTION till an IMAGINARY MAX no of filled rows, but it would be better if I could simply have it refer only till the last columns..

Code:

`=SUMPRODUCT(--(INDIRECT("'SP List'!$D$3:$D$6000")="Y")*(INDIRECT("'SP List'!$M$3:$M$6000")<>"Already Emailed")*((INDIRECT("'SP List'!$J$3:$J$6000")>=$N$1)*(INDIRECT("'SP List'!$J$3:$J$6000")<=$O$1)))`

Code:

`{=INDEX(INDIRECT("'SP List'!$A$3:$P$6000"),SMALL(IF((INDIRECT("'SP List'!$D$3:$D$6000")="Y")*(INDIRECT("'SP List'!$M$3:$M$6000")<>"Already Emailed")*((INDIRECT("'SP List'!$J$3:$J$6000")>=$N$1)*(INDIRECT("'SP List'!$J$3:$J$6000")<=$O$1)),ROW(INDIRECT("$A$3:$A$6000"))),ROW()-1)-2,2)}`

The first formula gives me the no of records meeting the criteria and the second formula is the commonly used INDEX(SMALL(IF- MULTIPLE LOOKUP ).

Can someone please convert this into VBA code where I can have it the LastRow unlike ROW-6000

Regards

all4excel