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:

Sub counta_ex()

Dim rng

Set rng = Range("B2:D6")


MsgBox ("Count of Cells with Data = " & WorksheetFunction.CountA(rng))

End Sub

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:

Sub counta_ex()

Dim cell As Range

Dim rng_counta As Range




'Range of B2:D14 including empty cells

Set rng_counta = Range("B2:D14")




For Each cell In rng_counta

     If Application.WorksheetFunction.CountA(cell) = 0 Then

         cell.Interior.Color = vbRed

     End If

Next cell

End Sub

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:

Sub counta_ex()

Dim cell As Range

Dim rng_counta As Range




'Range of B2:D11 including empty cells

Set rng_counta = Range("B2:D11")




For Each cell In rng_counta

     If Application.WorksheetFunction.CountA(cell) = 0 Then

         cell.Interior.Color = RGB(0, 128, 640)

     Else

         cell.Interior.Color = RGB(242, 163, 111)

     End If

Next cell

End Sub

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:

Sub counta_ex()

Dim cell As Range

Dim rng_counta As Range




'Range of B2:D14 including empty cells

Set rng_counta = Range("B2:D14")




'Delete all rows with empty cells in the range

For Each cell In rng_counta

     If Application.WorksheetFunction.CountA(cell) = 0 Then

         cell.EntireRow.Delete

     End If

Next cell

End Sub

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:

Sub counta_ex()

Dim cell As Range

Dim rng_counta As Range

'Range of B2:D11 including empty cells

Set rng_counta = Range("B2:D11")


'Assign N/A text to empty cells

For Each cell In rng_counta

     If Application.WorksheetFunction.CountA(cell) = 0 Then

         cell.Value = "N/A"

     End If

Next cell

End Sub

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:

Sub counta_ex()

Dim rng_counta1 As Range

Dim rng_counta2 As Range


'Two ranges count of data cells

Set rng_counta1 = Range("B2:D11")

Set rng_counta2 = Range("G4:I13")


MsgBox ("Count of Cells in Both ranges with data/text = " & WorksheetFunction.CountA(rng_counta1, rng_counta2))

End Sub

Result: