Excel VBA COUNTA: Count Nonblank Cells in Macros

In Excel VBA, count nonblank cells with Application.WorksheetFunction.CountA and pass it a range. For example, Application.WorksheetFunction.CountA(Worksheets("Data").Range("A2:A100")) returns the number of cells Excel considers nonempty in that range.

What COUNTA Does in Excel VBA

COUNTA is an Excel worksheet function, not a special VBA keyword. VBA can call it through Excel’s WorksheetFunction object, which lets a macro use the same counting rules as the COUNTA function entered in a worksheet. Its purpose is to count cells containing something, rather than to add numeric values or count only numbers.

COUNTA counts cells containing numbers, text, logical values such as TRUE or FALSE, errors, and formulas. It also counts a formula cell when the formula displays an empty string. That last rule can surprise people: a cell showing nothing is not necessarily an empty cell to Excel.

For example, suppose A2:A6 contains the number 12, the text “North,” a formula that returns an empty string, a cell with the error value #N/A, and a genuinely empty cell. COUNTA returns 4. The formula cell and error cell count; the genuinely empty cell does not.

A Basic VBA Example

This macro counts entries in A2:A100 on a worksheet named Data, then displays the result. The range is explicitly qualified through the worksheet variable, so the code does not accidentally count cells on whichever sheet happens to be active.

Sub CountEntries()
    Dim ws As Worksheet
    Dim target As Range
    Dim entryCount As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("A2:A100")

    ' Count cells Excel considers nonempty.
    entryCount = Application.WorksheetFunction.CountA(target)
    MsgBox "Nonblank cells: " & entryCount, vbInformation
End Sub

Change Data and the range address to match the workbook. The macro uses ThisWorkbook, meaning the workbook that contains the VBA code. If the code is stored in a different workbook from the data, use the appropriate workbook reference instead. Before running a macro that changes a workbook, save a copy: macros cannot be undone with Ctrl+Z. This particular example only displays a count and does not change cell contents.

Where to Put and Run the Macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the code into the module, and update the worksheet name if necessary. Return to Excel and run the macro from the editor, or use Developer > Macros, select CountEntries, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); a standard .xlsx file cannot store VBA code. Excel may show a security warning when opening a macro-enabled file. Only enable macros when you trust the file and its source. Excel for the web can open and edit many workbooks, but it cannot create, edit, or run VBA macros. Open the file in the desktop Excel application to use this code. Excel for Mac also supports VBA, though menus and keyboard shortcuts differ from Windows.

Choosing a Range to Count

A fixed range such as A2:A100 is straightforward when the data has a known size. It is also useful when a worksheet contains a title, headers, totals, or notes that should not be included. Set the range to the data rows only, rather than counting a whole column and then trying to subtract unrelated cells.

For a changing list, a macro can find the last used row in a particular column and build a range from it. This approach assumes that column A is a reliable guide to the list: if entries in A can be missing while other columns contain records, the last row found this way may not include the entire dataset.

Sub CountGrowingList()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim entryCount As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Row 1 is a header; count only rows below it.
    If lastRow >= 2 Then
        entryCount = Application.WorksheetFunction.CountA( _
            ws.Range("A2:A" & lastRow))
    Else
        entryCount = 0
    End If

    MsgBox "Nonblank cells: " & entryCount, vbInformation
End Sub

If the records are stored in an Excel table, a table column can be a clearer target than a guessed last row. Table names and column names make the intent easier to understand, and tables generally expand as data is added. Be precise about whether the header or totals row belongs in the count; the desired answer depends on what you mean by a record.

COUNTA Compared with COUNT and Worksheet Formulas

Use COUNTA when the question is “How many cells contain something?” Use COUNT when the question is “How many cells contain numbers?” For example, if a range has 3 numeric values, 2 text entries, and 1 blank cell, COUNT returns 3 while COUNTA returns 5. COUNT does not treat ordinary text as a number to count.

The worksheet equivalent is =COUNTA(A2:A100). With the data in A2:A100, this formula returns the same count as the VBA example. A worksheet formula updates as cells change, which is convenient when people need to see the count. VBA is more suitable when the count is one step in a larger process, such as checking whether an import contains any entries before a macro continues.

In VBA, Application.WorksheetFunction.CountA(target) is a clear way to call the function. Application.CountA(target) is another valid form. For ordinary COUNTA calls on a valid range, both provide the Excel count; WorksheetFunction makes it especially clear that the code is invoking a worksheet function. The result is a count, so storing it in a Long variable is appropriate for normal worksheet ranges.

Important Edge Cases: Formulas, Spaces, and Errors

COUNTA checks whether a cell contains a value or formula, not whether the cell looks visibly filled. A formula such as =IF(B2="","",B2) remains a formula even when it displays an empty string, so COUNTA includes that cell. This is a common reason a macro’s result appears larger than the number of visible entries.

A truly empty cell is not counted.
A cell containing one or more spaces is counted. Spaces are text, even when they are hard to notice.
A formula returning an empty string is counted because the cell contains a formula.
A cell containing an error such as #VALUE! or #N/A is counted.
Formatting alone, such as a fill color or border applied to an otherwise empty cell, does not make the cell count as nonblank.

If the goal is to count cells with a visible, nonempty result and ignore formulas that return empty strings, COUNTA may not match the requirement. A custom loop can check each displayed value instead. This example excludes empty strings and truly blank cells, includes errors as occupied cells, and still counts cells containing spaces.

Sub CountNonemptyResults()
    Dim ws As Worksheet
    Dim cell As Range
    Dim entryCount As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    For Each cell In ws.Range("A2:A100").Cells
        ' Count errors as occupied; otherwise test the cell value's length.
        If IsError(cell.Value) Then
            entryCount = entryCount + 1
        ElseIf Len(CStr(cell.Value2)) > 0 Then
            entryCount = entryCount + 1
        End If
    Next cell

    MsgBox "Nonempty results: " & entryCount, vbInformation
End Sub

That loop answers a different question from COUNTA: it tests the resulting value rather than simply counting formula cells. If spaces should also be treated as empty, the code needs an additional trimming rule. Decide how to handle errors and whitespace before changing the test, because each choice changes what the reported count means.

Filtered and Hidden Rows

COUNTA counts nonblank cells whether their rows are visible or hidden. Filtering a list does not automatically change the count, and manually hiding a row does not remove its cells from the range. This is useful when the intended count is the total number of entries, regardless of what is currently shown.

For a count that responds to filtered or hidden rows, Excel’s SUBTOTAL function may be a better fit. The 103 function number uses COUNTA-style counting while excluding rows hidden by a filter and rows hidden manually. In VBA, the corresponding call is Application.WorksheetFunction.Subtotal(103, target). Check that this matches the intended treatment of hidden rows before substituting it: a visible-row count is not the same as a count of all records.

Common Problems and How to Fix Them

Subscript out of range: The worksheet name in the code may not exactly match a sheet tab. Check spelling and spaces, and confirm the sheet is in the workbook referenced by ThisWorkbook.
Unexpectedly high count: Look for formulas returning empty strings, cells containing spaces, and error values. These are all counted by COUNTA.
Unexpectedly low count: Confirm that the range covers every intended row and that the correct worksheet is being referenced. A fixed range can omit data added below its last row.
Macro does not run: Confirm the file is saved as .xlsm and opened in desktop Excel, then check the macro security prompt and whether the code is in a standard module.
Run-time error on a sheet reference: Use a valid worksheet object and a correctly spelled range. Avoid relying on unqualified Range references, which can point to the active sheet.

To diagnose a range problem, temporarily display the range address in a message box or inspect the target variable in the Visual Basic Editor. It is usually more reliable to verify the sheet and range explicitly than to change the counting method without first checking what cells the macro is examining.

Frequently Asked Questions

Does COUNTA count a formula that displays a blank?
Yes. A cell containing a formula is counted even if the formula returns an empty string and the cell looks blank. Use a value-checking loop instead if you need to exclude those formula results.

How do I count nonblank cells in a VBA range?
Assign the range to a Range variable, then use Application.WorksheetFunction.CountA(rng). Qualify the range with the worksheet you intend to count so the result does not depend on the active sheet.

Does COUNTA count cells with errors or spaces?
Yes. Error values and cells containing spaces are both counted as nonempty. COUNTA does not judge whether a value is useful or visibly readable.

What is the difference between COUNT and COUNTA in VBA?
COUNT counts numeric values, while COUNTA counts cells containing any value or a formula. Choose based on whether text and other nonnumeric entries should be included.

Will COUNTA ignore filtered-out rows?
No. It counts cells in the specified range even when their rows are filtered out or hidden. Use SUBTOTAL with function number 103 when you specifically need to count visible nonempty cells.

Can I run VBA COUNTA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run VBA.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.