Ranges, Unions and Intersections
Copy / Cut a Range
Insert a Range
Delete a Range
Select a Range
Ranges Collection
All Ranges are contained in a Ranges Collection.
This collection contains all defined Ranges.
When creating a new Range, it is added to the Ranges Collection.
When a Range is deleted it is deleted from the Ranges Collection.
But in your code, a Range is always created and deleted within a worksheet object.
The only time you will actually reference the Range Collection is for its two methods, Count and Item.
Range Object
Define a Range
A "Range" uses A1 notation.
wst.Range("A1:A10").Cells
is the same as
wst.Range("A1:A10")
This range contains the single cell at D5 :
Dim wkb As Workbook
Dim wst As Worksheet
Dim rng As Range
Set wkb = Active.Workbook
Set wst = wkb.Worksheets(1)
Set rng = wst.Range("D5")
You can define a range of more than one cell with the colon, :
Set rng = wst.Range("A2:D6")
The variable rng was assigned to this range. For the rest of the program we can reference it as rng
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.
Set rng = wst.Cell(5,3)
In this example, the Range is defined with two cell references.
The cell at row 4, column 2 is B4.. The cell at row 6, column 5 is E6.
Set rng = wst.Range(Cells(4,2), Cells(6,5))
The comma, , is used to combine two ranges into one. This is the Union operator.
Using Variables for Cell Reference
An advantage of using the Cells syntax for Ranges is that you can use variables
Set rng = wst.Cell(y,2)
y = 1
Do while wst.cells(y,2) <> ""
If wst.cells(y, 2) <> "" Then
y = y + 1
End If
Loop
In this example, the variable y starts at 1, which is the first row.
The range is defined as cell(y, 2) which is column 2. The row number will increase for each iteration.
The loop will end when it finds the first blank cell in column 2. It reads each row in column 2.
At the end of the loop, y will contain the row number of the first blank row.
Cells, Rows, Columns
Excel doesn't have a cell object. It doen't have a row or column object. These are referenced as range objects.
Set rng = Range("A1:C5").Cells
'This range contains 15 cells
Set rng = Range("A1:C5").Rows
'This range contains 5 rows
Set rng = Range("A1:C5").Columns
'This range contains 3 columns
Ranges, Unions and Intersections
Here is the Range notation - ("A2:D6"):
There are 20 highlighted cells.
A Union of ranges is written with a comma.
Set rng1 = wst.Range("B3:G5, C2:D9")
rng1.Interior.ColorIndex = 15
This combines the two ranges into one range.
An Intersection of ranges is written without a comma.
Set rng2 = wst.Range("B3:G5 C2:D9")
rng2.Interior.ColorIndex = 15
In this example only the overlapping cells are part of the range.
End property
This returns a Range that contains the end of the range to the top, bottom, left or right.
'The first non-empty cell up from B14, will be red.
wst.Range("B14").End(xlUp).Interior.ColorIndex = 3
'The last non-empty down from A10, will be red.
wst.Range("A10").End(xlDown).Interior.ColorIndex = 3
'The last non-empty cell to the right of B4 will be light blue.
wst.Range("B4").End(xlToRight).Interior.ColorIndex = 8
'This is set up as a range, of "B6" to Range("B6").End(xlToRight)). This will cause the range of B6 to the first non-empty cell to the right of B6 to have a green font.
wst.Range("B6", Range("B6").End(xlToRight)).Font.Color = vbGreen
Copy / Cut a Range
Range.Copy [destination]
Range.Cut [destination]
You only need to define the upper left cell of the destination.
Cut works the same as Copy but it removes the range from its original position.
Copy the range in cells A1 to C6 and copy it to the Range starting at E2.
This will copy the range to E2 to G7.