How to Use Countif in VBA in Microsoft Excel

We all knows that how we can use “Countif” function in Excel and how it will work. Now in this article, we are going to learn how to use COUNTIF function in VBA.

Q): I am wondering how I can count all the cells that contain greater than 50 in column A through macros.


image 1


We need to follow the below steps:

  • Click on Developer tab
  • From Code group select Visual Basic


image 2


  • Click on Insert then Module


image 3


  • This will create new module.
  • Enter the following code in the Module

Sub Countif()

Range(“B10″).Value = Application.WorksheetFunction.Countif(Range(“A2:A8″), “>” & 50)

End Sub

  • Press ALT + F8 shortcut key for opening Macro window & then select the macro.


image 5


  • Alternatively you can press F5 to run the code in VBA screen.
  • After executing the macro we will get the output refer below snapshot


image 6


In above snapshot with VBA code you will only see the result not the formula in cell B10. In this way we can use COUNTIF in VBA code with one criteria.






Users are saying about us...

  1. Jeyner Lopez

    Hi, nice post, Using VBA how is possible scroll up a column and count the numbers after some value, let say 1, and count how many time 2 follow 1, and so on until reach the set limit in the case.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube