VBA – Cells and Ranges in Excel (A bit more than the standard story)

Posted on June 25, 2019 by

Excel’s ranges and cells are both complicated and very simple. They are simple, because everyone has an idea what is a range and what is a cell, and they are complicated, because these are both:

Properties of the worksheet object
Properties of the range object

Yup, range is a property of the range object. Thus, something like this is a valid code in VBA:

Sub TestMe()
    Debug.Print Range("C1").Range(Cells(1, 1), Cells(2, 2)).Address
    Debug.Print Range("C1").Range("B2").Address
End Sub

Sub TestMe()

Debug.Print Range("C1").Range(Cells(1, 1), Cells(2, 2)).Address

Debug.Print Range("C1").Range("B2").Address

End Sub

Close your eyes and think for a couple of seconds what would be printed in the Immediate window after running the code above. If you manage to guess both of the results then you may treat yourself anything. Try them and let me know how it works.

Worksheet.Range Property

This is the classic Range in Excel. Using only this one, you probably never ever need to go to the other 3. If you are not sure what to use, you would probably need to use this. It is something like Worksheets(1).Range("A1:Z10") or just Worksheets(1).Range("B2"), if it is a single range. What is needed to know here, is to declare the parent worksheet (which is a must everywhere actually!) and to make sure you are using the key word Set, as this is an object:

Sub LoopFromA1ToE5()

    With Worksheets(1)
        Dim myRange As Range
        Dim myCell As Range
        
        Set myRange = .Range(.Cells(1, 1), .Cells(5, 5))
        For Each myCell In myRange
            Debug.Print myCell.Address
        Next
    End With
    
End Sub

Sub LoopFromA1ToE5()

With Worksheets(1)

Dim myRange As Range

Dim myCell As Range

Set myRange = .Range(.Cells(1, 1), .Cells(5, 5))

For Each myCell In myRange

Debug.Print myCell.Address

End With

End Sub

Worksheet.Cells Property

The Wokrsheet.Cells property in general declares the cells of a worksheet, using the coordination system from A1. E.g, there are about 5 different ways to obtain the cells property of a worksheet:

Sub DeclaringCells()
    
    With Worksheets(1)
        Dim myCell As Range
        Set myCell = .Cells(5, 5)   '$E$5
        Debug.Print myCell.Address
        
        Set myCell = .Cells(5)      '$E$1
        Debug.Print myCell.Address
        
        Set myCell = .Cells(, 5)    '$E$1 (same as above!)
        Debug.Print myCell.Address
        
        Set myCell = .Cells(4, "Z") '$Z$4
        Debug.Print myCell.Address
        
        Debug.Print .Cells.Address  '$1:$1048576
    End With
    
End Sub

Sub DeclaringCells()

With Worksheets(1)

Dim myCell As Range

Set myCell = .Cells(5, 5) '$E$5

Debug.Print myCell.Address

Set myCell = .Cells(5) '$E$1

Debug.Print myCell.Address

Set myCell = .Cells(, 5) '$E$1 (same as above!)

Debug.Print myCell.Address

Set myCell = .Cells(4, "Z") '$Z$4

Debug.Print myCell.Address

Debug.Print .Cells.Address '$1:$1048576

End With

End Sub

Range.Range Property

Here is the reason of the funky code from the beginning of the article (Please, do not ever use it in any VBA project, like never-ever). This range property, when is relative to the Range object. Thus, in our code, we had Range("C1").Range("B2").Address, which somehow returns D2, because this is the relation of B2 to C1 . E.g., if C1 was magically transformed to A1 , then its B2 should be D2 , in order to keep the relation of rows and columns. This is quite tough to get probably, thus try to imagine it with this picture:

It is a kind of magic…

It is probably easier to explain with the following example:

Sub YouAreMyB2()
    Dim myRange As Range
    Set myRange = Worksheets(1).Range("D2:Z10")
    Debug.Print myRange.Range("A1").Address '$D$2
    Debug.Print myRange.Range("B2").Address '$E$2
End Sub

Sub YouAreMyB2()

Dim myRange As Range

Set myRange = Worksheets(1).Range("D2:Z10")

Debug.Print myRange.Range("A1").Address '$D$2

Debug.Print myRange.Range("B2").Address '$E$2

End Sub

It is quite visible that the A1 range of D2:Z10 is the top left cell, e.g. D2

Range.Cells Property

What you need to know about the Range.Cells property, is pretty much that it gives the relative position by rows and columns of the top left cell of the range. And because the Range.Item property is the default one of the Range object the word Cells could be omitted. Please, do not omit this word, it makes code more readable:

Sub WhoNeedsCellsAtAll()

    Dim myRange As Range
    Set myRange = Worksheets(1).Range("D2:Z10")
    
    Debug.Print myRange.Cells(100, 100).Address '$CY$101
    
    Dim myCell As Range
    Set myCell = myRange(100, 100)
    Debug.Print myCell.Address                  '$CY$101

End Sub

Sub WhoNeedsCellsAtAll()

Dim myRange As Range

Set myRange = Worksheets(1).Range("D2:Z10")

Debug.Print myRange.Cells(100, 100).Address '$CY$101

Dim myCell As Range

Set myCell = myRange(100, 100)

Debug.Print myCell.Address '$CY$101

End Sub

Thank you for staying with me until the end of the article. I hope you are not confused now. 🙂

Tagged with: excel vba range, Range object, vba cells, vba range