Referring to Ranges in VBA

There are two main properties you can use to obtain a Range object reference in VBA: Range and Cells.

Range

The Range property will accept one or two arguments, with the second one being optional. When using only one argument, it must be an address string (which can include a workbook and/or worksheet name) or the name of a range; whereas if you use two arguments, the arguments can be Range objects, address strings (but these may not include workbook/worksheet names), or the names of ranges, or a combination of these. Hence all of the following are valid:

Range("A1")
Range("Sheet2!A1")
Range("[Book1]Sheet3!B1")
Range("some_named_range")
Range("A1", "B10")
Range("A1", Range("B10"))
Range(Range("A1"), Range("B10"))
Range("name1", "name2")

These however are invalid:

Range(Range("A1"))
Range("Sheet1!A1", "Sheet1!B10")

Note: A Range object can only refer to cells on one worksheet. You cannot refer to cells on different sheets with one Range object.

 

Cells

The Cells property will accept one or two arguments, with the second one being optional. When using only one argument, it must be a number which is an index from right to left then top to bottom. When using two arguments, the first is a Row index and the second a Column index. The Row index must be a number, but the column index can be a number or letter.

The following are all valid forms of syntax:
Cells(2)     – refers to B1 (the second cell from left to right)
 
Cells(1, 1)      – refers to A1 (row 1, column 1)
 
Cells(1, "C")  – refers to C1 (row 1, column C)
 
Both the Range and Cells properties can be applied to the Application and Worksheet Objects, as well as to Range objects.

When applied to the Application object, the properties generally refer to the currently active sheet but if you pass a range name as the only argument, that range can be on a different worksheet. However you cannot pass two range names as arguments unless they both refer to ranges on the active sheet.

When applied to a Worksheet object, all arguments must refer to ranges on that sheet.

When applied to a Range object, all addressing is relative to that range but the returned Range reference does not have to be part of the original range.

Additionally, when using the Cells property of a Range object, the row and column index numbers can be 0 or negative.

For example:
Range("C3").Range("A1") – refers to cell C3 (first row, first column of C3)
Range("C3").Range("B1") – refers to cell D3 (first row, second column of C3, even this goes outside the original range)
Range("C3").Range("B1:C2") – refers to cells D3:E4 (from first row, second column to second row, third column of C3)
 
and some examples using Cells:
Range("C3").cells(1) – refers to cell C3 (first cell, going left to right)
Range("C3").Cells(2) – refers to cell C4 (because the initial range only has one column, cell 2 is the next row)
Range("C3:D4").Cells(3) – also refers to cell C4 (going from left to right, top to bottom)
Range("C3:D4").Cells(1, 1) – refers to C3 (first row and first column of start range)
Range("C3:D4").Cells(0) – refers to cell B3 (this is an oddity!)
Range("C3:D4").Cells(0,1) – refers to cell C2 (0th row, so 1 row above the top left cell, and the first column)
Range("C3:D4").Cells(-1,0) – refers to cell B1 (two rows above the top left cell, one column to the left)
 
As you can probably see, this kind of referencing can get a little confusing! It’s usually easier to use the Offset property for this kind of thing.

One final point which often catches people out when passing range objects as arguments to the Range property is that if you need to specify a worksheet object (which is good practice), you need to specify it for all of the Range/Cells properties you use.
 
So this is wrong:
 
Sheets("Sheet1").Range(Cells(1, "A"), Cells(10, "B"))
 
because the two Cells properties do not have a worksheet specified.
 
Although this may work some of the time, it will be dependent on where the code is (in a worksheet code module, unlike other modules, any use of Range or Cells without a worksheet object reference refers to the sheet containing the code and not the active sheet) and which worksheet is active at the time so sometimes it will fail – this kind of bug can be very hard to track down.
 
The correct syntax for the above is:
 
Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, "A"), Sheets("Sheet1").Cells(10, "B"))
 
or you can use a With … End With block to save a little typing:

With Sheets("Sheet1")
   .Range(.Cells(1, "A"), .Cells(10, "B"))
End With

 
Note the full stops (periods) before the Range and Cells calls.
 
 
I think that covers the basics for now – I will update this page as I think of other points to consider.

17 thoughts on “Referring to Ranges in VBA

  1. Dr(Physics!) Alan Elston says:

    Hi Rory.

    . I probably should of read this about 6 Months ago. Would have saved me (and you) a bit of work if I ‘ad done that.
    . You could probably use some of your answerers to me (and more sane versions of my questions!) for doing something along the lines of spelling it out clearly the whole business of Dimensioning, Capturing and manipulating Ranges as Range Objects, Arrays of Values and Arrays of… you know wot I mean!!

    . Alan

  2. shg says:

    Nice article, Rory.

    A reference style that I find intuitive, particularly in dealing with Solver or Goal Seek loops that operate row by row, is

    Rows(i).Range("A1:E1")
  3. Teky says:

    I understand how a range works by listing a starting and end part of the range. What I don’t understand is how Range(“A1048576”) works.

    • Sheeloo says:

      Try the following in the Immediate Window of VB Editor
      ?cells(1048576).address
      In Excel 2013 you will get
      $XFD$64

      Since there are 16384 columns in each row
      1048576/16384 gets you to row 64
      Remainder 1048512 will get you to XFD Column

  4. Pingback: Copy Entire Row to different sheet based on cell value - Page 2

  5. Pingback: test

  6. ginman says:

    Thank you very much for clarifying the need to specify the Worksheet object for the Cells Property. I was pulling my hair out trying to figure out why some of my Range statements would not work with Cells and they would work when using the letter:number references.

  7. Pingback: VBA - copy 2-dimensional array into 1-dimensional (single column)

  8. Alan says:

    I found this from the start of the Blog interesting..”… (which can include a workbook and/or worksheet name) or the name of a range…”
    So I see we have a way to use Range( ) to reference a specific Worksheet, in a Normal Code Module, even if that worksheet is not “Active”. Here the full string reference to cell A4 in the first’s worksheet Tab is made. The code can be run with any “Worksheet Active”. The word “Hi” will always be put in cell A! Of the first worksheet.
    Public SRstrRef As String
    Sub RangeSRstrRef3() ‘
    ‘ SRMakeStringRef to A4 in First Worksheet Tab
    Let SRstrRef = “‘” & Worksheets.Item(1).Name & “‘!A4”
    Let Range(SRstrRef).Value = “Hi”
    End Sub

    The above is clearly said in the Blog. But sometimes one often reads things like … “..Range( ) is an unqualified reference….” , which , as in this example, it is not always, which can confuse
    Alan

    • Alan says:

      _…also..

      _1 ) This code line
      Let Range(“‘” & Worksheets.Item(1).Name & “‘!A5”).Value = “Hi”
      will “work” in a normal macro module or the code module of The first Worksheet tab, Worksheets. Item(1)
      _. It will error in other Worksheet code modules.
      _2 ) One might have expected a version of a code line given in this Blog which puts “Hi” in Cell A6 of worksheet Object, Ws , such as this
      Let Ws.Range(Ws.Cells(6, 1), Ws.Cells(6, 1)).Value = “Hi”
      _.would not necessarily give us the expected results, if for example we omitted a Ws thus:
      Let Range(Ws.Cells(6, 1), Ws.Cells(6, 1)).Value = “Hi”
      _. and ran the code in a Normal Macro Module whilst a worksheet other than Ws was active.
      _…. In fact the code will, in such a case, still give us the desired “Hi” in worksheet Ws. It will give the same result if run from Worksheet Ws Code Module
      _. It will error if run in other Worksheet code modules.

      Possibly this can all be explained as follows: The syntax of Range is always
      Range(StringReferrence)
      In a Worksheets Code Module, Range refers to the Worksheet Members Range Objects in that Sheet. The StringReference must therefore refer to a Cell within the Worksheet, in full form, or shortened, ( like just “A6” ), in which case VBA possibly adds the missing bit at compile.
      In a Normal Macro Module Range refers to any Excel Application Member Range Object. Therefore any Range in any Worksheet can be referenced in the ( ) of Range( ). If the shortened form is used in a normal Macro Module, the missing bit will be taken at compile as that referring to the Active Worksheet
      In any Code Module we may refer to a Worksheets Range Object by transgressing through the Excel Application in such a way:
      Excel.Application.ThisWorkbook.Worksheets.Item(1).Range(
      In the example discussed here we effectively had
      Set Ws = Excel.Application.ThisWorkbook.Worksheets.Item(1)
      _. which is what Excel would have probably assumed if we had written a more familiar shortened form of
      Set Ws = Worksheets.Item(1)
      _…..
      I guess the bottom lines here are
      _(i) that what is in the ( ) in Range( ) actually determines “where we go”. Range ( ) will error if we “send” it to somewhere it does not have access to.
      _(ii) Range can only refer to the Worksheets Cells of the Code Module it is used in
      _(ii) Range in a normal Code Module can refer to all Application Cells, and not as often said, refers to the Active Worksheet, which is only the default sometimes, ( admittedly mostly )
      http://www.mrexcel.com/forum/excel-questions/817446-range%3D-equivalent-range-value%3D-sometimes-range-range-value-anomaly-2.html#post4038308
      http://www.mrexcel.com/forum/excel-questions/905371-selecting-range-different-worksheets-error-2.html#post4357655

  9. Alan says:

    Hi Rory
    I am wondering about this bit.. “….Both the Range and Cells properties can be applied to the Application and Worksheet Objects, as well as to Range objects…” .
    I am happy that a Worksheet Object, or a Range Object can have their respective Range and Cells Properties applied to them. These properties can be so used to return us a Range Object.
    The returned Range Object may be one or more cells, but noting, there is no Cells Object.
    Application_____Cells Property?
    I am not too happy with saying we can apply the Cells Property to the Application. I am wondering if VBA “guesses” or uses the Implied Implicit of Application.ActiveSheet. We are therefore not applying the Cells Property to the Application, but rather applying the Cells Property to the Active Worksheet to the Active Worksheet.
    Application_____Range Property?
    Further I am wondering if something of this form_..
    Application.Range(“RangeName” or “FullRangeReference”)
    _.. could be considered as referencing fundamentally a Range Object, that is to say the Application Range Object. Alternatively this could be regarded as a Range Method. In any case, an argument or syntax is required within the ( ).This may be a Range Name or a Full Range or one may further use the syntax utilising a Worksheet’s Cells or a Worksheet’s Range Property in a form such as this:
    Application.Range(Ws1.Range(“A1”), Ws1.Range(“A1”))
    The latter will be the VBA Implicit default for this written in a Normal Code module:
    Range(Ws1.Range(“A1”), Ws1.Range(“A1”))

    The following will only work if the Active Worksheet is Ws1
    Applicaion .ActiveSheet.Range(Ws1.Range(“A1”), Ws1.Range(“A1”))
    This last is referencing through the Range Property of Ws1
    The fact that_..
    Application .Range(Ws1.Range(“A1”), Ws1.Range(“A1”))
    _..will work in any code module, could, I suppose, suggest that the implicit default is
    Application .Ws1.Range(Ws1.Range(“A1”), Ws1.Range(“A1”))
    But as have seen no concrete documentation on this, I guess my Application Range Object is as good a guess as anyone’s..
    Alan

  10. Alan Elston says:

    I am wondering if this is correct..”… The Cells property will accept one or two arguments…”…
    As I see it there are two main Properties (disregarding here resize, and offset etc. ) that you can use to obtain a Range object reference in VBA: Range(“ “) type properties and the Range Item Property.
    There is no Property or Method taking such a form: Cells(row, column). In most ( not all ) situations where a code line including .Cells(row, column) is used, the .Cells can be omitted. The (row, column) in many such code lines is the default Range Item Property of a range. As such it may be written such, rather than in its full form of .Item(row, column). In the cases where .Cells can be omitted are when it is applied to a Range object.
    In cases where the .Cells cannot be omitted., the code line is again using the default range property which is the Range Item Property. The full code version would again be .Cells.Item(row, column)

    Cells(row, column) is simply a “way” to do it that looks nice. Cells is often used loosely to refer to what you “see” of the Range object in a spreadsheet. Cells(row, column) is therefore an easy thing to follow and understand in terms of a coordinate type referencing system
    I am not too sure if there is a Cells Item Property, even if intellisnse may offer it. In any case if there is, its use is as redundant as , in many ( not all ) cases the .Cells is
    Alan

    • romperstomper says:

      Respectfully, Alan, I’d have to disagree. There clearly is a Cells property that returns a Range object. I’m not really sure what else to say.

      • Alan Elston says:

        Hi Rory, Thanks for the reply. How are you 🙂
        I am trying to understand, so I may be wrong, .. but…
        As I understand it, Cells returns a Range object of all the cells in the object to which it is applied. This returned Range object can further have the Range Item Property applied to refer to individual cells within that Range object. ( or refer to individual cells outside that Range object also , should the original object be a Range within a worksheet )
        Fully written, we may have a code line, or part thereof, such as this
        Cells.Item(1, 2)
        As the Item property is the default Property of a Range object, we may omit the .Item. But we are using the Range Item Property to refer to the cell B1. We are using Cells to return a Range object comprising of all the cells in the Object to which it is applied

        A code line, often written as such
        Rng.Cells(1, 2)
        Is fully written as
        Rng.Cells.Item(1, 2)
        The .Cells is totally redundant here and can be omitted. The .Item can be omitted as previously. So we finally have this:
        Rng(1, 2)

        In the case of using .Cells to return a range object of all the cells in a worksheet, such as this
        Ws.Cells ( Often just written as Cells )
        The following
        Ws.Cells(1, 2)
        is again fully written
        Ws.Cells.Item(1, 2)
        Here .Cells is not redundant. One could , however also do it such
        Ws.Range(“A1”).Item(1, 2)
        and again this could be simplified to
        Ws.Range(“A1”)(1, 2)

        I am simply suggesting that the Cells(row, column) is a helpful and easy way to “show” what is being done, but that strictly speaking we do not have a Cells Property with arguments of this form Cells(row, column). The Cells Property, I am suggesting, possibly in my ignorance, has no arguments. ( I am not too sure if there may be a Cells Item Property. Considering the syntax possibilities shown, I expect it would be impossible to test for )

        Alan

Leave a Reply

Your email address will not be published. Required fields are marked *