In this Article
This tutorial will demonstrate how to use the Worksheet Range Property in VBA.
The Worksheet.Range Propety in VBA is used to specify a cell or group of cells in Excel. It returns the Worksheet.Range Object that represents the cell or cells and has a variety of methods available to it such as Select, Font, Copy and PasteSpecial to name but a few.
The syntax of the Worksheet.Range Property is simply:
where Cell1 is required, and [Cell2] is optional.
Therefore to refer to cell A1 in sheet 1 we would type:
To refer to cell A1 to A4 (including cells A2 and A3) in Sheet 1, we can refer to the first cell, then use a colon, and refer to the last cell in the range.
Alternatively, we can use the [Cell2] argument. This would also include cells A2 and A3 in the worksheet range.
If we want to refer to 2 cells that are non-contiguous, we can put a comma between the 2 cell addresses.
Worksheet.Range Properties and Methods
Once we have used the Range Property of the Worksheet object to refer to our Range, we can then use a variety of properties and methods that are now available to the Range Object to control the appearance and /or behavior of the cells using VBA.
To see the Properties and Methods of the Range Object, we can type a period after we have declared our range.
Properties will show up with the small ‘hand’ icon, while methods will show up with the small green ‘block’ icon.
One of the most common methods to use is the select method. This enables use to select specific cells or a cell in the Excel worksheet.
To select a single cell in a worksheet, we can just refer to the single cell in the Worksheet.Range Object
Sub SelectRange Worksheets("Sheet1").Range("B2").Select End Sub
To select a range of cells, we can refer to a range of cells in the Worksheet.RangeObject.
Sub SelectRange Worksheets("Sheet1").Range("B2:C5").Select End Sub
To select 2 non-contiguous ranges, we can use a comma between 2 ranges.
Sub SelectWorksheetRange Worksheets("Sheet1").Range("B3:B5, C2:F2").Select End Sub
Copy and PasteSpecial Methods
We can copy the information from cells to other cells using the Copy and PasteSpecial Methods.
Sub WorksheetRangeCopy() Worksheets("Sheet1").Range("B2:F5").Copy Worksheets("Sheet1").Range("B8").PasteSpecial xlPasteAll End Sub
Sub WorksheetRangeFont() Worksheets("Sheet1").Range("B3:B5, C2:F2").Font.Bold = True End Sub
We can use the Borders Property, and then the LineStyle property of the Borders property to put borders around all the cells.
Sub WorksheetRangeCopy() Worksheets("Sheet1").Range("B2:F5").Borders.LineStyle = xlContinuous End Sub
We can use the Current Region property of the Range object, and then the Select Method of the Current Region object to select all cells that are within the current region of the specified cell.
The current Region will select all the cells that are adjacent to the cell that is specified in the Range as long as those cells are contiguous. It will not select any cells after a blank row or column.
Sub CurrentRegion() Worksheets("Sheet1").Range("B2").CurrentRegion.Select End Sub
If we want to select all the cells that are populated in a worksheet, we need to use a Property of the Worksheet itself called UsedRange.
Sub UsedRangeSelect() Worksheets("Sheet1").UsedRange.Select End Sub