<div>
You could try Evaluate, where you can basically use your worksheet function.<br />
<br />
If lngGroup and lngSub are numeric<br />
<br />
result = Evaluate(&quot;SUMPRODUCT(--(A1<wbr />:A50=&quot; &amp;&nbsp;lngGroup &amp;&nbsp;&quot;), --(B1:B50=&quot; &amp;&nbsp;lngSub &amp;&nbsp;&quot;))&quot;)<br />
<br />
If they are strings:<br />
<br />
result = strformula = &quot;SUMPRODUCT(--(A1:A50=&quot;&quot;&quot; &amp;&nbsp;lngGroup &amp;&nbsp;&quot;&quot;&quot;), --(B1:B50=&quot;&quot;&quot; &amp;&nbsp;lngSub &amp;&nbsp;&quot;&quot;&quot;))&quot;<br />
<br />
<br />

</div>


You could try Evaluate, where you can basically use your worksheet function.

If lngGroup and lngSub are numeric

result = Evaluate("SUMPRODUCT(--(A1:A50=" & lngGroup & "), --(B1:B50=" & lngSub & "))")

If they are strings:

result = strformula = "SUMPRODUCT(--(A1:A50=""" & lngGroup & """), --(B1:B50=""" & lngSub & """))"




<div>
OK, try this formula:<br />
<br />
Range(&quot;A1:A&quot; &amp;&nbsp;Cells(65536, &quot;A&quot;).End(xlUp).Row &amp;&nbsp;&quot;,B1:B&quot; &amp;&nbsp;Cells(65536, &quot;A&quot;).End(xlUp).Row).Select<wbr /><br />
<br />
<br />
<br />

</div>


OK, try this formula:

Range("A1:A" & Cells(65536, "A").End(xlUp).Row & ",B1:B" & Cells(65536, "A").End(xlUp).Row).Select





<div>
imnorie,<br />
<br />
It worked as soon as I set the focus to shtGroup via Activate and removed the extra quotes for a numeric value.<br />
<br />
What is the point of the double negatives &quot;--&quot; at the beginning of each range?
</div>


imnorie,

It worked as soon as I set the focus to shtGroup via Activate and removed the extra quotes for a numeric value.

What is the point of the double negatives "--" at the beginning of each range?

<div>
<div class="content wysiwyg-content">
I need to count the number of rows in the range:<br />
<br />
shtGroups.Range(&quot;A1:B50&quot;)<br />
<br />
where the value in column A = lngGroup and the value in column B = lngSub<br />
<br />
I figured out how to do this in a cell formula, but I need to do it in vba.<br />
<br />

</div>
</div>


I need to count the number of rows in the range:

shtGroups.Range("A1:B50")

where the value in column A = lngGroup and the value in column B = lngSub

I figured out how to do this in a cell formula, but I need to do it in vba.



Count # of rows in a range that meet two criteria using VBA

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.