VBA/Excel/Access/Word/Excel/Range Reference

Activate the Range

 
Sub selectRange()
    Range("A1:A3").Activate
End Sub

Noncontiguous ranges

 
Sub non()
    range("A1:B8,D9:G16").Select
    
End Sub

Reference a range by name

 
Sub rangeName()
    range("PriceList").Select
    
End Sub

Refer to an entire column

 
Sub column()
    range("D:D").Select
    
End Sub

Refer to an entire row

 
Sub entire()
    range("3:3").Select
    
End Sub

Refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook

 
Sub sheetRange()
    Worksheets("Sheet1").range ("A1:C5")
End Sub

Refer to the B2 cell in DataInput, while another workbook is active

 
Sub ref3()
     Workbooks("Sales.xls").Worksheets("DataInput").Range("B2")
End Sub

returns a range object that represents the last non-empty cell in the same row

 
Function GetLastCellInRow(rg As Range) As Range 
    Dim lMaxColumns As Long 
    lMaxColumns = ThisWorkbook.Worksheets(1).Columns.Count 
    If IsEmpty(rg.Parent.Cells(rg.Row, lMaxColumns)) Then 
        Set GetLastCellInRow = _ 
            rg.Parent.Cells(rg.Row, lMaxColumns).End(xlToLeft) 
    Else 
        Set GetLastCellInRow = rg.Parent.Cells(rg.Row, lMaxColumns) 
    End If 
End Function

Select a range A1:A3

 
Sub selectRange()
    Range("A1:A3").Select
End Sub

Select a range by using the ActiveCell

 
     Sub MyMacro 
         ActiveCell.Range("A1:C1").Select
     End Sub

Select Last Cell

 
Public Sub SelectLastCell()
  Dim aRange As Range
  Dim LastRow As Integer
  Dim lastColumn As Integer
  
  Set aRange = Range("A1").SpecialCells(xlCellTypeLastCell)
  LastRow = aRange.Row
  lastColumn = aRange.Column
  
  MsgBox lastColumn
End Sub

Specifying Individual Cells with the Cells Property

 
Sub UsingCells() 
    Dim rg As Range 
    Dim nRow As Integer 
    Dim nColumn As Integer 
    Dim ws As Worksheet 
    Set ws = ThisWorkbook.Worksheets(1) 
    For nRow = 1 To 10 
        For nColumn = 1 To 10 
            Set rg = ws.Cells(nRow, nColumn) 
            rg.Value = rg.Address 
        Next 
    Next  
    Set rg = Nothing 
    Set ws = Nothing 
End Sub

The Offset method can also use negative arguments. A negative row offset refers to a row above the range. A negative column offset refers to a column to the left of the range.

 
Sub offsetNe()
    range("C2").offset(-1, -2).Select
End Sub

To refer to a range in a different workbook

 
Sub bookRange()
    Workbooks("Budget.xlsx").Worksheets("Sheet1").range ("A1:C5 ")
End Sub

Use 0 as one or both of the arguments for Offset: expression refers to cell A1

 
Sub zero()
    range("A1").offset(0, 0).Select
End Sub

Use ActiveCell.End to select a range

 
Sub ColumnSelect()
  Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Use disgonal cells to select a range

 
Sub Diagonal()
  Range("A10", "D1").Select
End Sub

Use the cell reference directly

 
Sub SelectIt()
  [A1:A10,C1:C10,E1:E10].Select
End Sub

Using the End Property to Navigate within a Worksheet

 
Sub ExperimentWithEnd() 
    Dim ws As Worksheet 
    Dim rg As Range 
    Set ws = ThisWorkbook.Worksheets(1) 
    Set rg = ws.Cells(1, 1) 
    ws.Cells(1, 8).Value = "rg.address = " & rg.Address 
    ws.Cells(2, 8).Value = "rg.End(xlDown).Address = " & rg.End(xlDown).Address 
    ws.Cells(3, 8).Value = "rg.End(xlDown).End(xlDown).Address = " & rg.End(xlDown).End(xlDown).Address 
    ws.Cells(4, 8).Value = "rg.End(xlToRight).Address = " & rg.End(xlToRight).Address 
    Set rg = Nothing 
    Set ws = Nothing 
End Sub

Using the Range Property to Refer to Groups of Cells

 
Sub UsingRange() 
    Dim ws As Worksheet 
    Dim rg As Range 
    Set ws = ThisWorkbook.Worksheets(1) 
    Set rg = ws.Range(ws.Cells(1, 1), ws.Cells(10, 10)) 
    rg.Value = 1 
    Set rg = ws.Range("D4", "E5") 
    rg.Font.Bold = True 
    ws.Range("A1:B2").HorizontalAlignment = xlLeft 
    Set rg = Nothing 
    Set ws = Nothing 
End Sub