Range Object

The Range object represents a cell, column, row, or group of cells in the Excel worksheet grid. The Cells property refers to all the cells of the specified Range object.  


How to reference the Range and Cells objects

Range uses the column letter and row number in quotations while Cells uses both column and row numerical values. The following expressions refer to the cell D4:

Range("D4")

or

Cells(4, 4)

which is the shorter and most commonly used version of Cells.Items(4,4)


ActiveCell refers to the unique cell active or highlighted at that moment.

 

To reference a range, use the colon as separator between the delimiting cells; and separate with a comma for multiple ranges as indicated below.

Range("A1:D4")

Range("A1:D4, H6:K10")

 

The following expression references the entire column A using the Range object. But it can also be done using the Columns property independently.

Range("A:A")

or

Columns("A")

 

Both Range and Cells can be declared as Range objects and defined as follows:

 Dim rng As Range
 Dim cell As Range
 Set rng = Range("A1:H8")
 Set cell = Cells(4, 8)

 

In all the examples above, the range refers to the active sheet. It would be the same as writing:

ActiveSheet.Range("A1:D4")

 

However, in some cases we need to reference a range in a particular sheet or even workbook (see worksheet and workbook VBA objects for more details).

Sheets("MySheet").Range("A1:D4")
 
Workbooks("MyWorkbook"). Sheets("MySheet").Range("A1:D4")

 

Assign/get a value to/from a range or cell

The following examples show how to assign or get a value to or from a range or cell using the Value property:

 MyValue = Range("A1").Value            'it gets the value from A1 into a variable
 Range("A1").Value = 100                  'it assigns the value 100 to cell A1
 Range("A1").Value = "Any Text"         'it assigns that text to cell A1
 Range("A1:D4").Value = 0                 'all cells in that range will have 0
 Range("A1:D4").Value = ""                'it removes any value in that range
 Cells(4, 8).Value = "This is row 4 and column 8"

 

To remove the content, we can also use the following methods:

 Range("A1:D4").Cells.Clear                 'it clears content and format in that range

or simply

 Range("A1:D4").Clear

 

 Range("A1:D4").ClearContent              'it clears only content in that range
 Range("A1:D4").ClearFormats              'it clears only format in that range

 

 ActiveSheet.Cells.Clear          'it clears content and format in all cells of the active sheet

 

We can format the value inside a range or cell using the NumberFormat property. It accepts all Excel’s known formats as well as custom formats. Let’s see some examples:

 Range("A1:A5").NumberFormat = "General"          'general number format
 Range("A1:A5").NumberFormat = "0.00%"            'percentage with two decimals
 Range("A1:A5").NumberFormat = "hh:mm:ss"       'time format


Select/Copy/Paste a range or cell

The below examples show how to select a single cell, a range, or multiple ranges with the Select method. We can activate and cell or range(s) within the selection; if nothing is selected, Activate works as Select.

 Range("D4").Select  or  Cells(4, 4).Select
 Range("A1:D4").Select
 Range("A1:D4, H8:M12").Select
 Range("A1:D4").Activate

 

The following example shows how to copy a range to other location:

 Range("A1:D4").Copy
 Range("H1").PasteSpecial
 
or simply
 
 Range("A1:D4").Copy Range("H1")


NOTE: To copy/paste a range from one sheet or workbook to another, you need to reference those objects in the statement. Have a look at the Excel Object Model in the tutorial for beginners if this is new to you. Here’s an example to copy a range from Sheet1 to Sheet2:

 Sheets("Sheet1").Range("A1:D4").Copy Sheets("Sheet2").Range("A1")


Formatting text, background, and border of a range or cell

There are three main attributes (also considered as objects themselves) to format a range or cell: The text (Font), the background (Interior), and the border (Borders). The table below shows the most common formatting properties for each of these three attributes of a range or cell.

Attribute (or object)

Properties

Font

Name, Size, Color or ColorIndex, FontStyle (or separately Bold, Italic, Underline)

Interior

Color or ColorIndex, Pattern

Borders

Color or ColorIndex, LineStyle, Weight



Font name (e.g. Arial, Calibri, etc), size, and style are straight forward properties that any Excel user should understand. The FontStyle can also be applied individually with the Booleans Bold, Italic, and Underline. Let’s see how the structure looks like:


 With ActiveSheet.Range("A1") '*
     .Font.Name = "Arial"
     .Font.Size = 12
     .Font.FontStyle = "Bold Italic"
 'or
     .Font.Bold = True
     .Font.Italic = True
     .Font.Underline = False
 End With

*the ‘With’ statement is explained in Chapter 3 (The Excel Object Mode) of the tutorial for beginners

 

The Color and ColorIndex properties can be applied to the Font, Interior, and Borders objects (and to some other VBA objects). There are three ways to add color with those two properties. The simplest is with the Excel VB color, which has a range of only 8 colours though:

 


 

Example: adding red color to the interior of Cell A1 using the VB Color property

 Range(“A1”).Interior.Color = vbRed

 

The ColorIndex property allows for 56 basic colors (see below), while the RGB property makes possible choosing from thousands of combinations of Red, Green, and Blue, setting a value for each of the three base colours between 0 and 255.

 


 

Example: adding red color to the interior of cell A1 using ColorIndex and RGB Color properties

 Range("A1").Interior.ColorIndex = 3
 Range("A1").Interior.Color = RGB(255, 0, 0)

 

The Borders object has two other commonly used properties on top of those mentioned earlier for Font and Interior. These are the LineStyle and Weight.

 

 

 Range("B2").Borders.LineStyle = xlContinuous
 Range("B2").Borders.Weight = xlThick

 

Furthermore, Borders accept attributes to target a particular border side as it can be seen in the following example, where the border is added specifically to one of the edges of the range or cell B2:

 Range("B2").Borders(xlEdgeBottom).LineStyle = xlDash

 


The BorderAround method of the range object is used to create a border around the whole range, and not individual cells as done before. It accepts the same attributes but follows a different structure. The following is setting a continuous red medium-weight border around the range B2:F8.

 Range("B2:F8").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=3

 

Formatting the dimensions and arrangement of ranges and cells

Other range properties allow to change the size of each individual cell based on the column width and row height, as well as aligning the content inside.

 Range("A1").ColumnWidth = 20
 Range("A:A").RowHeight = 40

also

 Columns(1).ColumnWidth = 20

 

Horizontal alignment can be set to xlLeft, xlCenter, and xlRight, while vertical alignment can be set to xlTop, xlCenter, xlBottom.

 Range("A1").HorizontalAlignment = xlCenter
 Range("A1").VerticalAlignment = xlBottom

also

 Rows(1).HorizontalAlignment = xlCenter

 

The Autofit property will set the width or height of the cells to fit its contents.

 Range("A1:A8").EntireRow.AutoFit
 Range("A1").EntireColumn.AutoFit


We can insert rows, columns, and cells with the Insert and Delete methods respectively. The methods accept the Shift attribute to determine the move of adjacent cells. Insert accepts xlShiftToRight and xlShiftDown, while Delete accepts xlShiftToLeft and xlShiftUp.

 Columns("B").Insert
 Columns("B:D").Delete
 Range("B2:D4").Delete xlShiftToLeft


We can also merge/unmerge cells as shown below. The Merge method set to True will merge cells for each row within the range.

 Range("A1:C1").Merge
 Range("C5:E7").Merge True
 Range("A1:C1").UnMerge


The Hidden property is used to hide/unhide columns and rows as follows:

 Columns("F:G").Hidden = True
 Rows("4:8").Hidden = False


We can lock/unlock cells as shown below. Note the worksheet needs to be protected to have effect (see protect/unprotect worksheets here).

 Range("A1:A9").Locked = True
 Range("A1:A9").Locked = False
 

 

Other VBA Objects

Application

Workbook

Worksheet

WorksheetFunction

Shape

Chart

 

No comments:

Post a Comment

Popular Posts