change the number of rows to a variable

[minrufeng]

Now, the number of rows in 'Data' sheet is 3938, so the function is:
=SUMPRODUCT((Data!G2:G3938=7100)*(Data!B2:B3938=1000)*(Data!O2:O3938=2)*(Data!C2:C3938))

If I don't know the number of rows is 3938, what should I do to accomplish the same function.

[Raman325]

Use:
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

This returns the number of the last used row in a worksheet

[theguz]

You could create a macro that when activated gives you the number of rows.

Dim i as integer

i = 0

Do until ActiveCell = ""
ActiveCell.Offset(1,0).Select
i = i +1
Loop

'At this point choose any cell that you want that value to be returned to
'for example
Range("D1").Value = i

I hope that this helps.

theguz

[minrufeng]

How to combine this into the sumproduct function in the original post? Thanks for your help.

Use:
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

This returns the number of the last used row in a worksheet