Excel Macro Beginners – What is Range Object in Excel VBA

.

Dear readers,

Before we discuss the Range Object technically, let us see what is RANGE in excel? In a very simple manner if I define Range, I can say that RANGE is a collections of cells which are in a sequence. This collection can have one or more than one cell.

As I have defined, Range is a collection of cells, but why in a sequence? Suppose I have a combination of cells A1, B2, D7 and E9. Will I call this combination as a Range? No, I can not call this collection as a Range because these cells are not in a sequence.
If I have a collection of Cells A1, B1, C1 and D1 then I can say that this is a Range from A1 to D1. Sequence of cells can be Vertical or Horizontal or both at the same time which will represent a range in a tabular format.

Now you know what is Range in Excel Workbook. To represent or access this cell range, the Object which is used in Excel VBA is called Range Object. Range Object is the most important and used Object in excel VBA. This article will give you an insight about some important and useful properties and methods of the Range Object.

As I said above, in this article you are going to learn some useful properties and methods of Range Object. Before you go there, you should know what is Property and Method of an Object in Excel VBA?

What is Property of an Object?

Property of an Object is something which has an Object. Basically it describes the Object.

What is Method of an Object?

Method of an Object is something which is performed on that Object.
This is the main difference between a property and method of an Object. Method is an action which is performed on an Object and Property is something which defines the object.

Syntax

Typically following is the syntax of referring a Range in Excel VBA:

Range(“A1:G5”)

Above statement will represent all the cells from A1 to G5 as shown in below pic:
 

Excel VBA - Rang Object

Excel VBA - Rang Object


 

With above example you can see that Range(“A1:G5”) is representing the whole area from Cell A1 to G5. This range is UNIQUE in only one WorkSheet because the same range can be found in other worksheet of the same WorkBook. Therefore it becomes necessary to instruct your VBA code to access Range(“A1:G5”) of WHICH SHEET? By default it assumes the range from the “Active Sheet”. So, if you want your code to access correct range of correct sheet then you should follow either of the below statements:


Worksheets("SheetName").Range("A1:G5").value
'***************OR*****************
Worksheets("SheetName").Activate
ActiveSheet.Range("A1:G5").value

Examples:

Let see in detail by referring to different examples.

How to read values from a Range Object

As you know a Range can hold one or more than one cell. When a Range is holding a single cell like Range(“A1”) then it will have a single value which can be stored in a variable easily like below:


Val = Range("A1").Value

But if your Range has more than one cell then it is stored in an Array format and Range(“A1:G5”).Value will return a Two dimensional array holding all the cells values of that range. To know more about reading such range and dealing with the Array read this article.

How to write values in to a Range in Excel VBA

Let us see writing values in a Range using Range Object in VBA.

i) Range with a Single Cell in Excel VBA


Range("A1").Value = Val 

Above statement will write the value of variable Val in Cell A1. This is simple and no issue with that.

i) Range with a Multiple Cells in Excel VBA


Range("A1:D1").Value = Val 

Now in this case variable Val has a single value whereas Range(“A1:D1”) has 4 cells. In such case that single value will be written in all the cells of the Range Object.

How to Select a Range through Excel VBA


Range("A1:D1").Select

Selection of a range is a Method. This method performs a Selection operation of the given range. This does not return anything hence no variable required to capture the returned value.

How to clear Contents of a Range


Range("A1:D1").ClearContents

This is another important method of Range Object. It is used to clear all the contents of a Range.
Note: It clears the data or formula written in a cell but it does not clear the formatting done on a cell or Range. To clear formatting done on a Range, you can use .ClearFormats.

Excel VBA to get total count of Rows in a Range

.Rows.Count is the property which returns the total number of rows in the Range.


Msgbox Range("A1:D4").Rows.Count

Above statement will return Rows Count as 4 because above Range(“A1:D4”) has got 4 rows (From Row 1 to Row 4).

How to get a total count of Columns in a Range

.Columns.Count is the property which returns the total number of Columns in the Range.


Msgbox Range("A1:D4").Columns.Count

Above statement will return Column Count as 4 because above Range(“A1:D4”) has got 4 Columns (From Column A to Column D).

Excel VBA to get count of cells in a Range

.Cells.Count is the property which returns the total number of Cells in the Range.


Msgbox Range("A1:D4").Cells.Count

Above statement will return total cell Count as 16 because above Range(“A1:D4”) has got 4 columns and 4 Rows hence the total number of cells are 4×4=16.

How to declare a Range Object in Excel VBA

You can define a Range object simply by following this syntax:

Dim myRangeVariable as Range

You can easily assign a Range to this variable as shown in below code:


Sub range_declaration()
Dim myRangeObject As Range
myRangeObject = Range("A1:D4")
End Sub

Now you can use your Range variable myRangeObject same as Range(“A1:D4”). It means you can use all the methods and properties of a Range Object can be used directly on myRangeObject variable.

I have tried to cover many Properties and Methods of Range Object but there are lot more properties and methods available on Range Object. In VBA code windows as soon as you press DOT (.) sign after Range(“A1:D4”) whole list of Methods and Properties will be displayed. Though Names given to every property and methods are self-explanatory still if you have any question or doubt, you can take help in Excel Help file. It is very rich. If you still need my help, ask me by writing your comment here.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. we

    how can i display the content of the range of cells in a message box? example i have values in A3, A4, A5.

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro CELLS() and RANGE() - Are they same? Are they Excel VBA Object? - [...] refer value of Cell D4, you can use Range(“B4″).Value. To know more about RANGE() you can read my previous…
  2. CELLS() and RANGE() - Are they same? Are they Excel VBA Object? - Welcome to LearnExcelMacro.com - […] To refer value of Cell D4, you can use Range(“B4”).Value. To know more about RANGE() you can read my…

Submit a Comment

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.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest