CODEWITHSUNDEEP
vbaexcel
DaveU
DaveU

Reputation: 1082

Range.Cells.Count vs Range.Count

I've reworded my original question.

Sub s()
  Dim r As Range
  Set r = ActiveSheet.Range("B2:D5")
  Debug.Print r.Rows.Count    'returns 4
  Debug.Print r.Columns.Count 'returns 3
  Debug.Print r.Cells.Count   'returns 12
  Debug.Print r.Count 'also returns 12
End Sub

In this particular scenario r.Cells.Count and r.Count are the same. I've come across this in numerous publications, where Cells is the default item if none is specified.

I'm just wondering if anyone has come across any Microsoft documentation that actually states this - so far I haven't.

Upvotes: 3

Views: 28100

Answers (3)

JohnyL
JohnyL

Reputation: 7152

In any situation, you need to ask yourself - what namely do I need? Number of cells? Of rows? Of columns? When you answer, then use appropriate actions:

1) Rows: rng.Rows.Count

2) Columns: rng.Columns.Count

3) Cells: rng.Cells.Count

As a tip, there's caveat with Rows property. If you want to iterate over rows and process their individual cells, then you must use Cells property of Range. Let's say, A1 cell contains value 123 and you want to retrieve it.

Sub G()

    Dim rng As Range
    Dim firstRow As Range
    Dim firstCell As Range

    Set rng = [A1:F10]
    Set firstRow = rng.Rows(1)

    Set firstCell = firstRow(1)
    '// DOES NOT WORK: Type mismatch
    MsgBox firstCell.Value

    Set firstCell = firstRow.Cells(1)
    '// WORKS OK! Shows: 123
    MsgBox firstCell.Value

End Sub

UPDATE

Also, when dealing with Range (especially in case of autofilter): the Range can contain non-contiguous range. The distinct contiguous ranges are located in Areas property, thus you will have to first iterate over Areas - and then process your range.

Sub G2
    Dim rng As Range, rngArea As Range, cell As Range
    For Each rngArea In rng.Areas
        For Each cell in rngArea.Cells '//or just "rngArea"
            '// Process further
        Next
    Next
End Sub

Upvotes: 2

Rory
Rory

Reputation: 34075

You don't actually know what you think you know. :)

Depending on how you assign the range, Range.Count may return the number of rows or columns, not the number of cells; Cells.Count will always return the number of cells. To demonstrate, try this:

Sub foo()
    Dim rg As Range
    Set rg = ActiveSheet.Columns(1)
    MsgBox "Cells count is: " & rg.Cells.Count & vbLf & "Count is: " & rg.Count
End Sub

Upvotes: 7

Michał Turczyn
Michał Turczyn

Reputation: 37500

Play with this code to see what they really like:

Option Explicit
Sub s()
    Dim rng As Range, rng2 As Range, rng3 As Range

    Set rng = Range("B2:D5")
    Set rng2 = rng.cells
    Set rng3 = rng2.cells 'rng.cells.cells

    rng3.cells(1, 1).Select
    'rng2.cells(1, 1).Select
    'rng.cells(1, 1).Select

End Sub

Bottom line is, that both, rng and rng.Cells are Range objects referring to the same set of cells. Now, since Range has method Count, they both have, and obviously they would return exactly the same. The difference is that using Range.Cells.Count you accessing property of property of Range object.

Additional resources:

Range.Cells Property

Range.Count Property

Upvotes: 1

Related Questions