• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Newbie questions about range objects in VBA

I have some newb questions about comparing ranges.

Say I have a pair of ranges: rng1 = wks1.Range("A1") and rng2 = wks2.Range("A2"). When I write "If rng1 = rng2 Then", does the computer check the values in those cells or that the references point to the same cell?

Is there a way to see which worksheet and cells a range object is referring to? When I add a range object to the watch window and dig around in the properties, I can't find the properties that refer to the worksheet and the cells.
  • 2
2 Solutions
Chris BottomleyCommented:
Hello ltdanp22,

To check the address ranges use for example

    Set rng1 = ActiveSheet.Range("a1:a2")
    Set rng2 = ActiveSheet.Range("a3:a4")
    Set rng3 = ActiveSheet.Range("a1:a2")
    Debug.Print rng1.Address = rng2.Address ' will return false
    Debug.Print rng3.Address = rng1.Address ' will return true


does the computer check the values in those cells or that the references point to the same cell<---it references the cell, not the value...so the value can change...

2) don't really understand...if you creating an instance of the worksheet, don't you already know...there's no x and y property?
It does actually compare the values of the ranges because the "Range" object has a hidden default property "_Default" and is returns the values. Depending on your ranges this can be different types or arrays, so it might fail to compare.

You can see what cells are in the range by using the "Row", "Col", "Rows.Count" and "Columns.Count" properties. Row and Col are the upper left cell and the count give the width and height of the range.
The "Col" property is named "Column", sorry.
Ah and there is a Worksheet property in the Range object.

If your declare your variables as type Range they might be more easy to use and VBA will autocomplete if you access their properties.

Dim myRange As Range
Set myRange = wks1.Range("A1")
myRange.               <autocomplete working now and you see the properties

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now