Referring to Ranges in VBA

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


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", "B10")
Range("A1", Range("B10"))
Range(Range("A1"), Range("B10"))
Range("name1", "name2")

These however are invalid:

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.



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.

34 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

  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
      In Excel 2013 you will get

      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 says:


      _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
      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:
      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 )

  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..

  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

    • 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 Elston says:

        Just following up, after extensive discussions throughout Blog Sites……. personal discussions with Experts etc…

        This is my conclusion:
        The Cells Property has a specific and unique definition: It returns all the cells, as Range objects (which is simultaneously also a single Range object of a single Areas area of contiguous cells). It returns them / it from the object to which it is applied.
        There is no Property of this form Cells(1, 2), or Cells(row_argument, column_argument). The use of, and described as , such Property, has crept in over the years.

        Cells(1, 2) is , from the syntax, correct in typical use, and returns in typical use a cell, as Range object through a row, column type coordinate referencing system. It can for convenience be thought of as a “Property” or even a “Method”, especially if the word “Method” is used loosely here to mean a “way of doing it”. Indeed many experts refer to this referencing as such! 🙂

        In a very typical use .Cells would be applied to a Worksheets object. It would then be claimed that the Cells(1, 2) Property is returning the second worksheet cell of B2.
        In fact the following is happening: The Cells Property is returning a Range object of all the cells of a Worksheet. The Range Item Property is then being applied to that Range object.

        The typical full code lines would be of this form:

        Sub B2FromRangeItemProperty()
        Dim Ws As Worksheet
         Set Ws = ThisWorkbook.Worksheets.Item(1) ' First Tab counting from the left
        Dim Rng As Range
         Set Rng = Ws.Cells
         Let Rng.Item(1, 2).Value = "This is second worksheet cell, B2"
        End Sub

        In VBA, the default Property is generally the Item Property, as is the case here. Therefore Rng.Item(1, 2) may be replaced by Rng(1, 2). ( Similarly Worksheets.Item(1) may be replaced by Worksheets(1) )
        In many typical uses, we might do away with the extra Range object variable, Rng, and apply the Range Item Property directly thus: Ws.Cells.Item(1, 2). Arguably we could refer here to .Item(1, 2)[cci_vb] in this case as the Item Property of Cells. In any case, the applied Property, the Range Item Property,  is once again the default. Hence this can be reduced to [cci_vb]Ws.Cells(1, 2)

        Furthermore, often one is careless, and relies on an unqualified Cells reference, that is to say, one does not use a Worksheets object variable. ( One could even be further careless and rely on the default .Value Property , and so neglect to use that as well ). The final simplified code would therefore be

        Sub B2FromRangeItemPropertySHimpfGlified()
        Cells(1, 2) = "This is second worksheet cell, B2"
        End Sub

        The latter code is from the syntax correct, and is achieving the same results as the former code. But we are in both cases referring to the second cell via the Range Item Property. Arguably one might refer to the latter case as using the Cells Range Item Property or the Cells Item Property. I personally have a feeling that we are in fact using the same Properties, that of the Range Item Property. Certainly, in my opinion, I think it is incorrect to refer to a Cells(row, column) type Property, that is to say there is , in my opinion, no Cells Property that takes arguments, unless specifically we mean by that the Cells Item property, but I believe that there is likely also no Cells Item Property, and that in all such cases where it may be referred to as so, we are, in fact, using the Range Item Property applied to the Range Object that the Cells Property has returned.


        • Alan Elston says:

          Typo, sorry, here you go:
          _……………………………………….Arguably we could refer here to .Item(1, 2) in this case as the Item Property of Cells. In any case, the applied Property, the Range Item Property, is once again the default. Hence this can be reduced to Ws.Cells(1, 2)

  11. Pingback: Error 1004

  12. shawn says:

    I need help using Range.
    I have two indices that should be referred back to a range. I mean I have X and Y and I need to specify the range [Ax:Ay]. can any one please help me?

    • romperstomper says:

      There are several ways:

      Range(“A” & x & “:A” & y)

      Range(“A” & x, “A” & y)

      Range(Cells(x, “A”), cells(y, “A”))

      for example.

      • Peter says:

        To be exact the syntax nowadays is ‘Path[Workbook]Sheet’!B1:C2 and

        Set myRange= Range(‘Path[Workbook]Sheet’!B1:C2)

        gives Nothing. Should the workbook be open and should I omit Path then?

        • Reg says:

          I think the point possibly is that

          <span class="vb">Range(“ “)</span>

          will not “work” to get the range object from a closed workbook. I suppose that would be a tricky thing for Excel to try and do.
          So the workbook must be open.
          However, interestingly/ strangely you find that you can either omit or include in your

          <span class="vb">refString</span>


          <span class="vb">Path</span>

          . It will still “work” with or without

          <span class="vb">Path</span>

          , provided that the workbook is open.
          You can also include an

          <span class="vb">=</span>

          if you like at the start of the

          <span class="vb">refString</span>

          . I do like to.

          It might be “safer” to include

          <span class="vb">Application.</span>

          when you are using this sort of “explicit syntax”, that is to say do it like:

          <span class="vb">Application.Range(refString)</span>

          Or, I like to do this:

          <span class="vb">Application.Range(“=” & refString)</span>

          By the way, if you are only interested in getting values , then you can use something similar to your

          <span class="vb">refString</span>

          , ( including the

          <span class="vb">Path</span>

          ) , to get those with the workbook, your

          <span class="vb">Book1</span>

          , opened or closed.
          I like to do that a lot.

    • Ludwig II says:

      Sorry for the late response MonkeyFace, I completely missed your post. ( Getting notifications is not default here, so I forget to check the box )
      For me it is practically useful because:
      I have some larger routines in which I use the closed reference often, so I often have a variable anyway containing that reference. So it is convenient to use that, and it is very helpful I find, to use the Application.Range with a full reference as I am less likely to fall into the traps mentioned in this Blog of inadvertently referencing the wrong range.
      Also I tend to use a very simple function as standard in most of my files to convert all column numbers to letters, so that means I can use my full reference way in the situations when I might have chosen the .Item(r, c) type way.
      All in all , for me I find it practically advantageous. (As a side effect It helps me avoiding awkward conflicts with people who don’t understand what the cells(r, c) thing is all about, which seems to be almost everyone… )

  13. Walter Rauschenberger says:

    This looks quite complete. However I do miss the way I prefer to use named ranges. In my code assigning a value to a named cell looks like this:
    wsMain.CustNo = …
    s = wsMain.CustNo
    The principles:
    1. Every sheet has a short though meaningful code name
    2. Every named range is a property of the worksheet and this looks like this
    Public Property Get CustNo() As String
    CustNo = Me.Range(“celCustNo”).Value
    End Property
    Public Property Let CustNo(ByVal s As String)
    Me.Range(“celCustNo”).Value = s
    End Property
    3. Any reference to a range is fully qualified. That means that there is no room for Excel to make assumptions which may or may not conform with what I intend.
    The advantages are numerous. Not only that there is no danger of typos but also a possibility to make a cell content type save.

    • romperstomper says:

      Although I can see the advantage of that, it seems like an awful lot of work having to create properties for every named range!

      • Walter Rauschenberger says:

        The work is once, the benefit is enormous. Not only the most compact and clear code you can think of but also the only way to make your code as independent as possible from the situation in which it is used.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.