asked on 10/6/2011

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

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.

Microsoft Excel Microsoft Office

Last Comment

Dale Fye

10/6/2011

Norie

10/6/2011

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 & """))"

Metallimirk

10/6/2011

OK, try this formula:

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

ASKER CERTIFIED SOLUTION

Norie

10/6/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Dale Fye

10/6/2011

ASKER

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?