Excel VBA: Get a Range’s Length in Cells or Characters

VBA & macros · Updated November 21, 2026

In Excel VBA, use Range.CountLarge to get how many cells a range contains; use Range.Rows.Count and Range.Columns.Count for its dimensions. If by “length” you mean the number of characters in a cell, use VBA’s Len function on that cell’s value instead—there is no single Range.Length property.

What “range length” can mean

A range has more than one useful size measure, so decide what you want to measure before choosing a property. A range can contain a number of cells, extend across a certain number of rows and columns, or hold text whose character length you want to inspect. Those are different questions, and VBA reports them in different ways.

  • Cell count: the total number of cells included in the range, whether blank or not.
  • Row count: the number of rows in the range.
  • Column count: the number of columns in the range.
  • Text length: the number of characters in a cell’s value after it is represented as a string.

For example, a range from B2 through D8 has 21 cells, 7 rows, and 3 columns. If B2 contains the text “Report,” its text length is 6. Asking for the “length” without specifying which of these you mean is a common source of VBA errors.

Count cells with CountLarge

For an ordinary range, Range.Count returns the number of cells. In newer Excel VBA code, Range.CountLarge is generally the safer choice: it can represent counts larger than the maximum value supported by a Long integer. That matters for very large ranges, though most small ranges will return the same result from either property.

The following example counts every cell in B2:D8 on a worksheet named Data and prints the result in the Visual Basic Editor’s Immediate window. Change the worksheet name and address to match your workbook.

Save a copy of the workbook before running a macro. Macros cannot be undone with Ctrl+Z, even when a particular example only reads data.

Sub ShowRangeCellCount()
    Dim ws As Worksheet
    Dim rng As Range

    ' Set the worksheet and range to measure.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set rng = ws.Range("B2:D8")

    ' Print the total number of cells, including blanks.
    Debug.Print rng.CountLarge
End Sub

The result is 21 because the range has three columns and seven rows. CountLarge counts the cells in the range; it does not check whether they contain values. A blank cell still contributes to the total.

Get rows and columns separately

Use the Rows and Columns collections when you need the dimensions rather than the overall number of cells. For the same B2:D8 example, rng.Rows.Count returns 7 and rng.Columns.Count returns 3. Multiplying those values gives 21 for a single rectangular area.

Sub ShowRangeDimensions()
    Dim ws As Worksheet
    Dim rng As Range

    ' Measure a rectangular range on the Data worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set rng = ws.Range("B2:D8")

    Debug.Print "Rows: " & rng.Rows.Count
    Debug.Print "Columns: " & rng.Columns.Count
End Sub

This approach is useful when code needs to loop through each row or column, resize an output area, or validate a table’s expected shape. It is also clearer than inferring dimensions from the total cell count: a range with 12 cells could be 3 by 4, 2 by 6, or another arrangement.

Measure the characters in a cell

To get the length of a cell’s displayed value as a string, read the value and pass it to Len. The example below reads A1 from the Data worksheet. It handles an Excel error value separately so the conversion does not attempt to treat that error as ordinary text.

Sub ShowCellTextLength()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim textLength As Long

    ' Read the value from the cell to measure.
    Set ws = ThisWorkbook.Worksheets("Data")
    cellValue = ws.Range("A1").Value2

    If IsError(cellValue) Then
        Debug.Print "A1 contains an Excel error value."
    Else
        textLength = Len(CStr(cellValue))
        Debug.Print textLength
    End If
End Sub

If A1 contains the text “Report,” the result is 6. A blank cell produces a length of 0. The conversion with CStr also means numeric values are measured as their string representation, not as a count of digits in the cell’s number format. A date stored as an Excel value, for example, may be represented differently from the formatted date you see on the worksheet.

For a formula cell, Value2 reads the formula’s calculated result, not the formula text. If you want the number of characters in the formula itself, use the cell’s Formula property and pass that string to Len. Keep those two questions distinct: the formula might be long even when it returns a short result.

Count nonblank cells instead of all cells

A frequent variation is “How many cells in this range contain something?” That is not the same as the range’s size. One option is Excel’s COUNTA worksheet function, called through VBA as WorksheetFunction.CountA. It counts cells with values, text, errors, or formulas. A formula that displays an empty string can still count as nonblank because the cell contains a formula.

Sub ShowNonblankCount()
    Dim ws As Worksheet
    Dim rng As Range
    Dim nonblankCount As Double

    ' Set the cells whose contents should be counted.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set rng = ws.Range("B2:D8")

    nonblankCount = Application.WorksheetFunction.CountA(rng)
    Debug.Print nonblankCount
End Sub

Use this when the goal is to count occupied cells, not to determine how many cells the range spans. If “nonblank” has a special meaning in your task—for instance, excluding formulas that return empty text—define that rule explicitly and use a method that checks the values against it.

Noncontiguous ranges and used-range pitfalls

A range can contain multiple separate areas, such as A1:A5 and C1:C5 selected together. CountLarge counts the cells in all those areas, so that example contains 10 cells. However, row and column properties on a multi-area range can be unintuitive: they do not necessarily describe the overall bounding rectangle spanning every area. If your code accepts noncontiguous ranges, inspect each area separately through the range’s Areas collection.

Another common trap is assuming UsedRange means “all cells with meaningful data.” Excel’s used range can include cells that were formatted or edited and later cleared. Its size may therefore be larger than the visible data set. For a reliable record count, identify the relevant column or table and state what qualifies as a record rather than relying blindly on the worksheet’s used range.

For example, to find the last nonempty row in column A, code can start at the bottom of that worksheet column and move upward:

Sub ShowLastRowInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Find the last cell with content in column A.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Debug.Print lastRow
End Sub

This is a common way to identify a data boundary, but it has assumptions: column A must be a suitable column to check, and a formula that returns an empty string is still a formula in the cell. If column A has gaps or does not contain a value for every record, choose a different column or use an Excel Table’s data body range.

Choosing the right property

Match the property to the information your code needs. For a rectangular range, these choices are straightforward:

  • Use CountLarge for the total number of cells, including empty cells.
  • Use Rows.Count for the number of rows, or Columns.Count for the number of columns.
  • Use Len(CStr(cell.Value2)) for the character count of a cell’s value, after guarding against error values when appropriate.
  • Use WorksheetFunction.CountA when you want a count of nonempty cells under COUNTA’s rules.

These properties are part of Excel’s VBA object model and work in Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA and these range properties, though the Visual Basic Editor interface and available keyboard shortcuts can differ. Excel for the web can open a workbook containing macros, but it cannot create, run, or edit VBA macros; use the desktop Excel application for the examples here.

Frequently Asked Questions

Does a Range object have a Length property?
No. Use CountLarge for the cell count, Rows.Count or Columns.Count for dimensions, and VBA’s Len function for text length.

Does Range.Count include blank cells?
Yes. It counts the cells in the range regardless of whether they are blank. Use a content-counting method such as WorksheetFunction.CountA if you need to count cells that are not empty.

How do I get the number of cells in a range in VBA?
Use Range.CountLarge, such as Worksheets("Data").Range("B2:D8").CountLarge. That example returns 21 because the range has seven rows and three columns.

How do I count characters in a cell with VBA?
Read the cell’s value and pass its string representation to Len, for example Len(CStr(Range("A1").Value2)). Check for an Excel error value first if the cell might contain an error.

Why does my range count differ from my number of records?
A range count includes every cell, including blanks. A record count usually means rows that meet a condition. Choose a column or table that identifies records, then count those qualifying entries rather than using the range’s total cell count.

Related

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.