Lesson 18: The Range Object

The range is one of the most important and most commonly used Excel VBA 365 objects. In fact, we have dealt with the Range object in several previous lessons. The range object has many properties and methods, let's explore.

18.1 The Select Method

The Range object contains two arguments that specify a selected area on the spreadsheet. The syntax is

Range(starting_cell,Ending_ Cell)

You can use the cells reference system to define the range, as follows:

Range("Ai:Cj")

Or using the cells(i,j) object, as follows:

Range(cells(i,j),cells(m,n))

*i stands for row and j stands for column

For example, to select the range from cell A1 to C6, the syntax is

Range("A1:C6").Select

where select is a method of the Range object.

The above example is the same as the following code:

Range(cells(1,1),cells(3,6)).Select

Example 18.1

Private Sub CommandButton1_Click()
 Range("C1:C6").Select
End Sub
 

*you can use lowercase c1:c6

18.2 The Columns Property

The columns property of the Range object is to select certain columns in the particular range specified by the Range object.

The syntax is

Range(starting_cell,Ending_ Cell).Columns(i).Select

Example 18.2

This example select column C in the range A1 to C6

Private Sub CommandButton1_Click()
Range("A1:C6").Columns(3).Select
Range("A1:C6").Columns(3).Interior.Color = vbBlue
Range("A1:C6").Columns(3).Font.Color = vbYellow
End Sub

You can also use Cells(1,1) to Cells(6,3) instead of A1:C6, the syntax is

Range(Cells(1,1),Cells(6,3)).Columns(3).Select

18.3 Using With Range...End With

You can also format font the cells in a particular column in terms of type, color, bold, italic, underlined and size using the With Range…..End With Structure. It can also be used to format other Range properties like the background color. Using With Range….End With structure can save time and make the code cleaner.

Example 18.3

Private Sub CommandButton1_Click()
With Range("A1:C6").Columns(2)
.Font.ColorIndex = 3
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Font.Name = "Times New Roman"
.Font.Size = 14
.Interior.Color = RGB(255, 255, 0)
End With
End Sub

18.4 The Rows Property

Basically, the syntax for the Rows property is similar to that of the Columns property, you just need to replace Columns with rows.

The syntax of selecting a row within a certain range is

Range(starting_cell,Ending_ Cell).Rows(i).Select

Example 18.4

This following code selects the third row within the range A1 to F3

Private Sub CommandButton2_Click()
 Range("A1:F3″).Rows(3).Select
End Sub