• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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.

0
Dale Fye
Asked:
Dale Fye
  • 2
1 Solution
 
NorieCommented:
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 & """))"


0
 
MetallimirkCommented:
OK, try this formula:

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



0
 
NorieCommented:
Oops, that last one's a mistake - I changed to use a variable for the formula.

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

Using a variable kind of helps when debugging, as does posting the right code.
0
 
Dale FyeAuthor Commented:
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?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now