VBA Range.Count: How to Count Cells, Rows, and Columns

In Excel VBA, Range.Count returns the number of cells in a Range object, including blank cells. For example, a range from A1 through C10 contains 30 cells; use CountLarge when a range might exceed 2,147,483,647 cells.

What Range.Count Means

Count is a property of Excel’s Range object, not a worksheet function. It tells you how many cells are represented by the range address. It does not inspect cell contents, so a blank cell counts just as much as a cell containing a number, text, a date, or a formula.

The property is useful when a macro needs to check the size of a block before processing it, or when code needs to know how many cells are in a selected range. The result describes the range itself, not the number of rows with data or the number of nonblank entries. Those are different questions and need different properties or counting functions.

For example, A1:C10 has 10 rows and 3 columns, so its cell count is 10 × 3 = 30. If only A1 and C10 contain values, Range.Count still returns 30.

Basic Syntax and a Working Example

The syntax is expression.Count, where expression is a Range object. In practice, specify the worksheet as well as the address so the macro does not depend on whichever sheet happens to be active. The example below assumes the workbook containing the macro has a worksheet named Sheet1.

Save a copy of the workbook before running any macro. Macros cannot be undone with Ctrl+Z, even when they appear to make only a small change.

Sub ShowCellCount()
    Dim target As Range

    ' Set the range to count on Sheet1.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
    Debug.Print target.Count
End Sub

Run the procedure and inspect the Immediate window in the Visual Basic Editor; it prints 30. This example only reads the range and prints a result, so it does not alter cell values. If Sheet1 does not exist in the workbook that contains the code, VBA raises a subscript-out-of-range error. Change the sheet name to match the workbook.

You can also use Range("A1:C10").Count inside a procedure, but an unqualified Range refers to the active worksheet. Explicitly naming the worksheet makes code more predictable, particularly when a macro activates another sheet or is run while a different workbook is active.

Counting Rows and Columns Instead of Cells

When you need the dimensions of a range, use its Rows.Count or Columns.Count property. These report the number of rows or columns in the range, rather than multiplying the two dimensions to get a cell total.

For a range from B2:D8, Rows.Count is 7 and Columns.Count is 3, while Count is 21. Here is a short procedure that prints all three results. It assumes Sheet1 exists in the workbook containing the macro.

Sub ShowRangeDimensions()
    Dim target As Range

    ' B2:D8 is seven rows by three columns.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2:D8")
    Debug.Print target.Count
    Debug.Print target.Rows.Count
    Debug.Print target.Columns.Count
End Sub

The Immediate window shows 21, 7, and 3, in that order. A common source of confusion is that Rows.Count on a multirow range reports the range’s row count, while Columns.Count reports its column count. If you instead ask the worksheet for ws.Rows.Count, you get the number of rows in the entire worksheet: 1,048,576 in modern Excel worksheets.

For just the row count of a column-sized range such as A:A, use Range("A:A").Rows.Count. It returns the worksheet’s full row capacity. Likewise, Range("1:1").Columns.Count returns the worksheet’s full column capacity.

Blank Cells, Nonblank Cells, and Values

Because Range.Count counts cells rather than entries, it is not a substitute for counting filled cells. If a 20-cell range has 12 populated cells and 8 blanks, Count returns 20. For a VBA procedure that needs to count nonblank cells, use Excel’s CountA worksheet function through WorksheetFunction.

For instance, with the range A1:A20 on Sheet1, Application.WorksheetFunction.CountA(target) counts cells that contain values or formulas. A formula that displays an empty string is still a formula in a cell, so it is counted by CountA. That detail matters when a sheet uses formulas to make unused rows look blank.

Choose the counting method that matches the question:

Use Range.Count to count every cell in a range, blank or populated.
Use WorksheetFunction.CountA for cells containing something, including text and formulas.
Use WorksheetFunction.Count when the goal is to count numeric values.
Use WorksheetFunction.CountIf when only cells meeting a condition should count, such as values greater than zero.

These worksheet functions answer questions about cell contents; Range.Count answers a question about the size of a Range object. If the distinction is unclear, compare a range with known blank cells before using the count to drive a larger process.

Noncontiguous Ranges and Multiple Areas

A Range object can represent more than one separate block of cells. Such a range is often called a multi-area range. For example, the two areas A1:A3 and C1:C2 contain five cells altogether. The count applies to the cells represented by the Range object, not just to the first area.

To examine the separate blocks, use the range’s Areas collection. Each area is itself a Range object, so its Count can be inspected separately. This is helpful when a macro works on a noncontiguous selection, though it is often simpler and safer to process each area in a loop rather than assume one continuous rectangle.

Be careful when combining a range count with row or column counts for a multi-area range. A multi-area object is not necessarily one rectangular grid, so its overall row and column dimensions may not describe the shape you have in mind. If your logic depends on each block’s dimensions, inspect each item in Areas individually.

Count Versus CountLarge

For ordinary ranges, Count is convenient. Its result is limited by the maximum value of a VBA Long, 2,147,483,647. A whole modern Excel worksheet has 1,048,576 rows and 16,384 columns, or 17,179,869,184 cells in total, which is beyond that limit.

Use CountLarge when a range could contain more than 2,147,483,647 cells, such as a whole worksheet or a very large multi-area range. Excel provides this property to handle counts beyond the limit of Count. For a small range such as A1:C10, either property is appropriate; for a whole-sheet count, prefer CountLarge.

Do not assume that a large worksheet count tells you how much data is actually present. Counting the entire worksheet counts all of its cells, including the many that are empty. To estimate populated data, count a deliberately bounded area or use an appropriate content-counting method instead.

Common Errors and Reliable Practices

Many mistakes come from confusing the range’s dimensions with its contents, or from relying on Excel’s active sheet. Use a fully qualified range, then choose the property or worksheet function that matches the intended result.

Counting blanks by accident: Count includes blanks. Use CountA or another content-based function if you mean populated cells.
Using the wrong sheet: An unqualified Range can refer to the active sheet. Qualify it with ThisWorkbook.Worksheets("Sheet1") or the intended workbook and worksheet.
Confusing rows with cells: target.Rows.Count gives the row count; target.Count gives the total cells.
Overflow on very large ranges: Use CountLarge when the count might exceed the maximum value of a Long.
Expecting a content count from a selection: A selected block can contain blank cells. The selection’s size says nothing about how many entries are filled.

A further practical point: Count does not tell you whether cells are hidden, filtered out, or visually blank. It counts the cells in the range regardless of whether their contents are currently visible. If a task concerns only visible cells or only displayed records, it needs additional logic rather than a plain cell count.

Frequently Asked Questions

Does VBA Range.Count include blank cells?
Yes. It counts every cell in the specified range, whether the cell is empty, contains a value, or contains a formula.

How do I count rows in a VBA range?
Use target.Rows.Count, where target is the Range object. For a range B2:D8, the result is 7.

What is the difference between Count and CountA?
Count is a Range property that returns the number of cells in the range. CountA is a worksheet function that counts cells containing something, including text and formulas.

When should I use CountLarge?
Use CountLarge when the range might contain more than 2,147,483,647 cells. A whole modern worksheet exceeds that limit, even though typical data ranges do not.

How can I count columns in a range?
Use target.Columns.Count. For B2:D8, that returns 3, because the range spans columns B, C, and D.

Choosing the Right Count

Think first about what you are trying to measure. If the question is “How many cells are in this address?”, use Range.Count, or CountLarge for very large ranges. If you need the number of rows or columns, use the corresponding collection’s Count. If you mean “How many cells contain data?” or “How many match a condition?”, use a content-counting worksheet function instead. Keeping those questions separate prevents common counting errors and makes VBA code easier to understand.

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.