Range and cells object in Excel VBA

The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. This chapter gives an overview of the properties and methods of the Range object. Properties are something which an object has (they describe the object), while methods do something (they perform an action with an object).

There are two objects Cells and Range, Range always represent cells. This object wil confuse you little, i have given few Example for easy understanding.

Cells Example 1.

Place command button and add the below codes and se the result.

Cells(1, 1).Value = "Hellow world"

Result When you click the Button on the Worksheet.

Example 2

Cells(2, 1).Value = 2

Range Example 1.

Place command button and add the below codes and se the result.

Range(“A1:a5”).Value = “15”

Result When you click the Button on the Worksheet

Example 2

Range("A1:A2,B3:C4").Value = 10

I Hope you understood different between cells and Range.

Now This time to understand how to Declare the range.

Place command button and add these code and see the example.

Option Explicit

Sub Button1_Click()

Dim i As Range

Set i = Range("a1:c5")

i.Value = "25"

End Sub

Out put

Select statement

An important method of the Range object is the Select method. The Select method simply selects a range.

Code:

Dim pactice As RangeSet practice = Range("A1:C4")
practice.Select

Out put.

Rows

The Rows property gives access to a specific row of a range.

Code:

Dim practice As Range
Set practice = Range("A1:C4")

practice.Rows(3).Select

Out Put

Column

The Columns property gives access to a specific column of a range.

Code:

Dim practice As Range
Set practice = Range("A1:C4")

practice.Columns(2).Select

Result:

Copy/Paste statement

The Copy and Paste method are used to copy a range and to paste it somewhere else on the worksheet.

Code:

Range("A1:A2").Select
Selection.Copy

Range("C3").Select
ActiveSheet.Paste

Result:

Although this is allowed in Excel VBA, it is much better to use the code line below which does exactly the same.

Range("C3:C4").Value = Range("A1:A2").Value

Clear content

To clear the content of an Excel range, you can use the ClearContents method.

Range("A1").ClearContents

or simply use:

Range("A1").Value = ""

Note: use the Clear method to clear the content and format of a range. Use the ClearFormats method to clear the format only.

Count function.

With the Count property, you can count the number of cells, rows and columns of a range.

Note: border for illustration only.

Example code

Dim example As Range
Set example = Range("A1:C4")

MsgBox example.Count

Result:

Example code.

Dim example As Range
Set example = Range("A1:C4")

MsgBox example.Rows.Count

Result:

Do you like this page.? Please register your Email Id for News Letter..!!