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
CountLargefor the total number of cells, including empty cells. - Use
Rows.Countfor the number of rows, orColumns.Countfor 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.CountAwhen 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
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Include Double Quotes in StringsIn Excel VBA, put two double-quote characters inside a string literal wherever you want the resulting text to…
- Excel VBA Copy and Paste Formulas Between Cells SafelyTo copy a formula to another cell with Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteFormulas. This pastes…
- Excel VBA AutoFilter Tables: Setup, Code, and Common FixesTo filter an Excel table with VBA, use the table’s Range.AutoFilter method and specify the column’s position within…
- Excel VBA Variant: What It Is and When to Use ItIn Excel VBA, a Variant is a flexible data type that can hold different kinds of values, including…
- Excel VBA MATCH Error Handling: Avoid Run-Time ErrorsFor safer Excel VBA MATCH error handling, use Application.Match and test its result with IsError before using it.…
- Excel VBA Arrays vs Collections vs DictionariesIn Excel VBA, use an array for a compact, indexed set of values, a Collection for an ordered…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.