VBA CountA method

What is CountA method in VBA

The CountA is an Excel function that you may use in VBA as well.

In VBA, you may use it by WorksheetFunction object.

CountA function returns counts of cells that are not empty (in range or array).

Alternatively, CountA returns the count of cells in range or array with data.

Syntax:

expression.CountA (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

Let us explore this function in the examples below with VBA code and Excel sheet data.

A simple example of CountA

For our examples below, consider this Excel sheet:

We will create a range of B2:B6 in VBA. You can see above, a few cells are empty in that range.

We will get the count of cells with data by using CountA and display it in the message box:

Result:

You can see the range is highlighted in the above graphic. CountA only returned cells with data.

Highlighting all cells that are empty in the given range by CountA

Now let us see the usage of CountA function.

In the following example, we will change the background color of all empty cells in our range by using CountA method. This time our range is B2:D14

Code:

Result:

Changing empty and non-empty cells colors using CountA               

In this example, we have empty cells in between rather than whole empty rows.

The task is to give empty cells bluish background and non-empty cells orange color.

Code:

Result:

Delete empty rows example by using CountA method

For this example, again consider the sample sheet we shared above. It contains three empty rows.

The target is to delete rows with empty cells by using CountA method. Here is the solution:

Result:

You see, we used EntireRow.Delete after finding the cells with empty data for removing rows.

Writing some text/value in empty cells example

In this example, we will write N/A in all empty cells found in the range.

For that, we have deleted text in different cells in our sample sheet.

Code:

Result:

Get the total cells with data in two ranges

As shown in the syntax of CountA, you may pass up to thirty arguments in the method (ranges or arrays).

In this example, we will pass two ranges in the CountA method to get the count of cells with data in both ranges.

The result is displayed in the message box:

Result: