Working with Ranges
Printer Friendly Page
Ranges are a very important part of the Excel Object.
You will use Ranges more often then any other object.
A
Range can be as small as a singe cell, or as large as a group of cells that can span several worksheets.
Define a Range
This range contains the single cell at
D5:
Range("D5")
This range contains cells
A1 to
C3:
Range("A1:C3")
Define a Range Using a cell Reference
With a cell reference, the row is listed first, then the column.
In this example, the cell at row 5, column 3 is
C5.
Cell(5,3)
Define a Range using Cell references
In this example, the cell at column 4, row 2 is
D2.
The cell at column 6, row 5 is
F5.
Range(.Cells(4,2), .Cells(6,5))
Copy a Range
Copy the range in cells A1 to C3 and copy it to the Range starting at D5.
This will copy the range to D5 to F8.
Range("A1:C3").copy range("D5")
Cut a Range
Range("a1:c3").cut range("d5")
Delete a Range
range("a1:c3").delete xlshiftup xlshiftdown
Select a Range
range("a1:c3").select
Name a Range
range("b3:c6").name = "lqtests"
Assign a variable to a Range
Dim rg as Range
Set rg = rev.Range("A1:D5")
In this example, the Range object
rg is set to cells A1:D5 on the worksheet assigned to the variable
rev.