Here we discuss how to use COUNTA Function in Excel VBA along with practical examples and downloadable excel template.Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More. You can use arguments manually in the VBA COUNTA function and it still works.The empty cells will not be counted using the COUNTA function and will be ignored.However, it does not consider the cell which is empty. COUNTA considers all the values like numbers, strings, error values, Booleans, empty text (“”).You can use VBA COUNTA function when you have to count the number of non-empty cells present in the given range.You better could see the worksheet and navigate through column A. You may not be seeing at all the 17 through this screenshot as it is a partial one. Which means that, we have 17 non-empty cells present in column A of the worksheet.
Step 8: Run this code by hitting the F5 or Run button and you can see the output as shown below in cell B1 of an active sheet from the workbook. Op_cell = WorksheetFunction.CountA(rng_1) Therefore, this function under worksheet function object class can count the non-empty cells present within the entire column A. Step 7: Use rng_1 variable as an input argument under COUNTA. Navigate towards the COUNTA function and double click it to select. Step 6: As soon as you hit dot (.) after WorksheetFunction object, you can access all the functions available under that class. Step 5: Use WorksheetFunction object on the right-hand side of assignment operator (it would be an expression that will get evaluated and value will be stored under cell set to variable op_cell) to initialize the class within which we can access as well as use COUNTA function. This can be considered as an output initialization. Step 4: Now, use general assignment operator against op_cell variable, so that we can store the output under the cell which is set to that variable. op_cell is set to cell B1 as it would be the cell that contains the output of the COUNTA. Here, rng_1 variable has been set to the range of entire column named A. This is to be done because we can’t directly assign a value to a variable defined as a range object.ĭim rng_1 As Range Dim op_cell As Range Set rng_1 = Range("A:A") Step 3: Now, set the range for both of the variables using VBA Set property. The output of COUNTA will be stored under op_cell variable. Here, rng_1 will store the input range for COUNTA function. One variable will be helpful to hold the range of input argument column and other variables will be helpful in holding the cell where we want the output as a counted number.ĭim rng_1 As Range Dim op_cell As Range End Sub Step 2: Define two new variables under this sub-procedure as a Range using Dim. Step 1: Define a new sub-procedure which can hold your macro. See the partial screenshot of the data below. As the entire column consists of more than 10 Lac’s rows, it is an ideal time-consuming for me to navigate towards and count each non-empty cell. Suppose I have data all across column A and I need to count what are the non-empty rows in the entire column. Example #2 – VBA COUNTA to Count Non-Empty Cells in a Given Range In this code, we want the system to count the input arguments and store the count under cell A2 of sheet Example 1. Step 7: Run this piece of code by hitting F5 or Run button and see the output under cell A2 of sheet “ Example 1” in the active workbook. We have tried to bring all the data types as an argument to this function. Step 6: Specify the arguments manually under the COUNTA function. Select COUNTA from the list of multiple available functions which allows you to count the non-empty cells or values. Step 5: Put a dot (.) after object specified and you will see a list of functions available to use under it. Sheets("Example 1").Range("A2") = WorksheetFunction Add the object named WorksheetFunction so that we can access COUNTA function under it. Step 4: Use an assignment operator so that we can assign the value to the cell that is going to be accessed through the code above.
This piece of code selects the cell A2 as a range from the sheet named Example 1. See the following screenshot for your reference: In order to do that, we need to use Sheets.Range method in VBA.
I want to store it in cell A2 of sheet named “ Example 1”. Step 3: Decide where we want to store the output for the COUNTA function. Step 2: Define a new sub-procedure within the newly inserted module, that can hold your macro. Step 1: Insert a new module in Visual Basic Editor (VBE).