Range Object

Description

Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range.

Accessors

Here are several examples of how to use the most important accessor properties and methods of the Range object.

Range Method

Use the Range method to return a single cell or range of cells. The following example places the value of cell A1 in cell A5.

Worksheets("Sheet1").Range("A5").Value = _
    Worksheets("Sheet1").Range("A1").Value

The following example fills the range A1:H8 with random numbers by setting the formula for each cell in the range. When it is used without an object qualifier (an object to the left of the period), the Range method returns a range on the active sheet. If the active sheet is not a worksheet, the method will fail. Use the Activate method to activate a worksheet before you use the Range method without an explicit object qualifier.

Worksheets("sheet1").Activate
Range("A1:H8").Formula = "=rand()"    'Range is on the active sheet

The following example clears the contents of the range named "Criteria."

Worksheets(1).Range("criteria").ClearContents

If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation).

Cells Method

Use the Cells method to return a single cell by specifying the row and column. The following example sets the value of cell A1 to 24.

Worksheets(1).Cells(1, 1).Value = 24

The following example sets the formula for cell A2.

ActiveSheet.Cells(2, 1).Formula = "=sum(b1:b5)"

Although you can also use Range("A1") to return cell A1, there may be times when the Cells method is more convenient because you can use a variable for the row or column. The following example creates column and row headings on the worksheet named "Sheet1." Notice that once the worksheet has been activated, the Cells method can be used without an explicit sheet declaration (it returns a cell on the active sheet).

Sub SetUpTable()
Worksheets("sheet1").Activate
For theYear = 1 To 5
    Cells(1, theYear + 1).Value = 1990 + theYear
Next theYear
'
For theQuarter = 1 To 4
    Cells(theQuarter + 1, 1).Value = "Q" & theQuarter
Next theQuarter
End Sub

Although you could use Visual Basic string functions to alter A1-style references, it's much easier (and much better programming practice) to use the Cells(1, 1) notation.

Cells and Range Methods

You can also use the Cells method to return part of a range by specifying the row and column relative to the upper-left corner of the range. The following example sets the formula for cell C5.

Worksheets(1).Range("c5:c10").cells(1,1).formula = "=rand()"

You can also use the Cells method inside the Range method to specify the start and end cells for the range. The following example sets the border line style for cells A1:J10.

With Worksheets(1)
    .Range(.Cells(1, 1), .Cells(10, 10)).Borders.LineStyle = xlThick
End With

Notice the period in front of each occurrence of the Cells method. The period is required to apply the result of the preceding With statement to the Cells method—in this case to indicate that the cells are on worksheet one (without the period, the Cells method would return cells on the active sheet).

Offset Method

The Offset method returns a range at a specified offset to another range. The following example selects the cell three rows down and one column to the right from the upper-left cell of the current selection. You cannot select a cell that is not on the active sheet, so you must first activate the worksheet.

Worksheets("sheet1").Activate  'can't select unless the sheet is active
Selection.Offset(3, 1).Range("a1").Select

Union Method

Use the Union method and Range method to return multi-area ranges (ranges composed of two or more contiguous blocks of cells). The following example creates an object called myMultiAreaRange, defines it as the union of ranges A1:B2 and C3:D4, and then selects it.

Dim r1, r2, myMultiAreaRange As Range
Worksheets("sheet1").Activate
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select

If you work with selections containing more than one area, the Areas method is very useful. It divides a multi-area selection into individual Range objects and then returns them as a collection. You can use the Count property on the returned collection to check for a selection containing more than one area, as shown in the following example:

Sub NoMultiAreaSelection()
    numberOfSelectedAreas = Selection.Areas.Count
    If numberOfSelectedAreas > 1 Then
        MsgBox "You cannot carry out this command " & _
            "on multi-area selections"
    End If
End Sub

Properties

AddIndent Property, Application Property, Column Property, ColumnWidth Property, Count Property, Creator Property, CurrentArray Property, CurrentRegion Property, Dependents Property, DirectDependents Property, DirectPrecedents Property, EntireColumn Property, EntireRow Property, Font Property, Formula Property, FormulaArray Property, FormulaHidden Property, FormulaLocal Property, FormulaR1C1 Property, FormulaR1C1Local Property, HasArray Property, HasFormula Property, Height Property, Hidden Property, HorizontalAlignment Property, Interior Property, Left Property, ListHeaderRows Property, LocationInTable Property, Locked Property, Name Property, Next Property, NumberFormat Property, NumberFormatLocal Property, Orientation Property, OutlineLevel Property, PageBreak Property, Parent Property, PivotField Property, PivotItem Property, PivotTable Property, Precedents Property, PrefixCharacter Property, Previous Property, Row Property, RowHeight Property, ShowDetail Property, SoundNote Property, Style Property, Summary Property, Text Property, Top Property, UseStandardHeight Property, UseStandardWidth Property, Value Property, VerticalAlignment Property, Width Property, Worksheet Property, WrapText Property.

Methods

Activate Method, Address Method, AddressLocal Method, AdvancedFilter Method, ApplyNames Method, ApplyOutlineStyles Method, Areas Method, AutoComplete Method, AutoFill Method, AutoFilter Method, AutoFit Method, AutoFormat Method (Range Object), AutoOutline Method, BorderAround Method, Borders Method, Calculate Method, Cells Method, Characters Method, CheckSpelling Method, Clear Method, ClearContents Method, ClearFormats Method, ClearNotes Method, ClearOutline Method, ColumnDifferences Method, Columns Method, Consolidate Method, Copy Method, CopyFromRecordset Method, CopyPicture Method, CreateNames Method, CreatePublisher Method, Cut Method, DataSeries Method, Delete Method, DialogBox Method, End Method, FillDown Method, FillLeft Method, FillRight Method, FillUp Method, Find Method, FindNext Method, FindPrevious Method, FunctionWizard Method, GoalSeek Method, Group Method, Insert Method, Item Method, Justify Method, ListNames Method, NavigateArrow Method, NoteText Method, Offset Method, Parse Method, PasteSpecial Method (Range Object), PrintOut Method, PrintPreview Method, Range Method, RemoveSubtotal Method, Replace Method, Resize Method, RowDifferences Method, Rows Method, Run Method, Select Method, Show Method, ShowDependents Method, ShowErrors Method, ShowPrecedents Method, Sort Method, SortSpecial Method, SpecialCells Method, SubscribeTo Method, Subtotal Method, Table Method, TextToColumns Method, Ungroup Method.