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

Posted on 2011-10-05
Last Modified: 2012-05-12
I need to count the number of rows in the range:


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.

Question by:Dale Fye (Access MVP)
    LVL 33

    Expert Comment

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

    LVL 6

    Expert Comment

    OK, try this formula:

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

    LVL 33

    Accepted Solution

    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.
    LVL 47

    Author Closing Comment

    by:Dale Fye (Access MVP)

    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?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now