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

Activate the Range

   <source lang="vb">

Sub selectRange()

   Range("A1:A3").Activate

End Sub

</source>
   
  

Noncontiguous ranges

   <source lang="vb">

Sub non()

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

End Sub

</source>
   
  

Reference a range by name

   <source lang="vb">

Sub rangeName()

   range("PriceList").Select
   

End Sub

</source>
   
  

Refer to an entire column

   <source lang="vb">

Sub column()

   range("D:D").Select
   

End Sub

</source>
   
  

Refer to an entire row

   <source lang="vb">

Sub entire()

   range("3:3").Select
   

End Sub

</source>
   
  

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

   <source lang="vb">

Sub sheetRange()

   Worksheets("Sheet1").range ("A1:C5")

End Sub

</source>
   
  

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

   <source lang="vb">

Sub ref3()

    Workbooks("Sales.xls").Worksheets("DataInput").Range("B2")

End Sub

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Select a range A1:A3

   <source lang="vb">

Sub selectRange()

   Range("A1:A3").Select

End Sub

</source>
   
  

Select a range by using the ActiveCell

   <source lang="vb">

    Sub MyMacro 
        ActiveCell.Range("A1:C1").Select
    End Sub
</source>
   
  

Select Last Cell

   <source lang="vb">

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

</source>
   
  

Specifying Individual Cells with the Cells Property

   <source lang="vb">

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

</source>
   
  

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.

   <source lang="vb">

Sub offsetNe()

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

End Sub

</source>
   
  

To refer to a range in a different workbook

   <source lang="vb">

Sub bookRange()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub zero()

   range("A1").offset(0, 0).Select

End Sub

</source>
   
  

Use ActiveCell.End to select a range

   <source lang="vb">

Sub ColumnSelect()

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

End Sub

</source>
   
  

Use disgonal cells to select a range

   <source lang="vb">

Sub Diagonal()

 Range("A10", "D1").Select

End Sub

</source>
   
  

Use the cell reference directly

   <source lang="vb">

Sub SelectIt()

 [A1:A10,C1:C10,E1:E10].Select

End Sub

</source>
   
  

Using the End Property to Navigate within a Worksheet

   <source lang="vb">

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

</source>
   
  

Using the Range Property to Refer to Groups of Cells

   <source lang="vb">

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

</source>