The range is one of the most important and most commonly used Excel 2010 VBA 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.
The Range object contains two arguments that specify a selected area on the spreadsheet. The syntax is
For example, to select the range from cell A1 to C6, the syntax is
where select is a method of the Range object.
Private Sub CommandButton1_Click() Range("A1:C6").Select End Sub
The columns property of the Range object is to select certain columns in the particular range specified by the Range object.
The syntax is
This example select column C in the range A1 to C6
Private Sub CommandButton2_Click() Range("A1:C6").Columns(3).Select End Sub
You can also use Cells(1,1) to Cells(6,3) instead of A1:C6, the syntax is
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.
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
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
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