Range Object in VBA



The Range Object is the object you will use most within your Excel VBA. Before you can manipulate any region within Excel, you must express it as a Range object:

Dim myRange As Range
Set myRange = ActiveSheet.Range("A1")

and work with methods and properties of that range:

myRange.Delete Shift:=xlUp

A Range object represents a cell, a row, a column, a selection of cells containing one or more blocks of cells or even a group of cells on multiple sheets:

' Cells
Set myRange = ActiveSheet.Range("A1:D5")
 
' Row
Set myRange = ActiveSheet.Rows(1)
 
' Column
Set myRange = ActiveSheet.Columns(2)
 
' Group of Cells
Set myRange = Application.Union( _
    ActiveSheet.Range("A1:D1"), _
    ActiveSheet.Range("C3:C5"))
 
' Select method
myRange.Select