Range Object Methods With Excel VBA

The Range object has a large collection of methods, giving developers the ability to program a wide variety of actions—from copying a range to the clipboard to solving nonlinear equations.

The most commonly used methods of the Range object include:

• Activate
• AddComment
• AutoFill
• AutoFit
• BorderAround
• Clear
• ClearComments
• ClearContents
• ClearFormats
• ClearNotes
• Copy
• CopyPicture
• Cut
• DataSeries
• Delete
• FillDown
• FillLeft
• FillRight
• FillUp
• Find
• FindNext
• FindPrevious
• FunctionWizard
• GoalSeek
• Insert
• PasteSpecial
• Replace
• Select
• Show

You can find detailed information about each method in the VBA Help system. Below are some basic examples.

Activating and Selecting a Range

• The Activate method makes a specific range the active one.
• The Select method highlights a range and returns a Selection object.

For example, the code below first activates cell A2, assigns it the value 1, then selects the range A3:A4 and assigns the value 3 to the selected cells:

Range("A2").Activate
ActiveCell.Value = 1
Range("A3:A4").Select
Selection.Value = 3

Automatically Adjusting Range Size to Fit Data

The AutoFit method automatically adjusts column width and row height to fit the entered data.

The following example demonstrates how to use AutoFit when creating the header row of a report table:

Sub DemoAutoFit()
    Range("A1").Value = "June"
    Range("B1").Value = "July"
    Range("C1").Value = "August"
    Columns("A:C").AutoFit   
    Range("D1").Value = "Total Sales Volume"
    Range("D1").Columns.AutoFit
End Sub

Filling a Range with a Single Value

• FillDown – fills a range from top to bottom, copying the values from the top row into all other cells of the range.
• FillUp – fills a range from bottom to top, copying the values from the bottom row.
• FillLeft – fills a range from right to left, using the values from the rightmost column.
• FillRight – fills a range from left to right, using the values from the leftmost column.

Example: The following instruction copies the value from cell A10 into every cell in the range A1:A9:

Range("A1:A10").FillUp

Adding Borders Around a Range

The BorderAround method applies a border around a range.

BorderAround(LineStyle, Weight, ColorIndex, Color)

• LineStyle (optional) – specifies the line style. Possible constants:
  xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlLineStyleNone, xlSlantDashDot.
• Weight (optional) – specifies the thickness. Possible constants:
  xlHairline, xlMedium, xlThick, xlThin.
• ColorIndex (optional) – specifies the color from the current palette. Constants:
  xlColorIndexAutomatic, xlColorIndexNone.
• Color (optional) – specifies the color using the RGB model.

Example: The following instruction applies a thick double green border around the range A1:B2:

Range("A1:B2").BorderAround LineStyle:=xlDouble, Weight:=xlThick, _
    Color:=RGB(0, 255, 0)

Clearing a Range

• Clear – clears everything (content, formatting, comments, notes).
• ClearComments – clears only comments.
• ClearContents – clears only the values and formulas (but keeps formatting).
• ClearFormats – clears only formatting.
• ClearNotes – clears notes.

Example: The following instruction clears the range A1:G37:

Range("A1:G37").Clear

Copying, Cutting, and Deleting Data

• Copy(Destination) – copies the range to another range or to the clipboard.
  • If Destination is omitted → copied to the clipboard.

Example: Copy range A1:D4 to E5:H8 on Sheet2:

Range("A1:D4").Copy Worksheets("Sheet2").Range("E5")

• Cut(Destination) – cuts (copies + deletes) the range to another range or to the clipboard.

Example: Cut A1:D4 from Sheet1 to the clipboard:

Worksheets("Sheet1").Range("A1:D4").Cut

• Delete – deletes a range.

Example: Delete the third row of the active worksheet:

Rows(3).Delete

Paste Special

The PasteSpecial method pastes data from the clipboard with special options.

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

• expression – a Range object that specifies the top-left cell of the paste area.
• Paste (optional) – specifies what to paste. Constants include:
  xlPasteAll, xlPasteAllExceptBorders, xlPasteColumnWidths, xlPasteComments, xlPasteFormats, xlPasteFormulas, xlPasteFormulasAndNumberFormats, xlPasteValidation, xlPasteValues, xlPasteValuesAndNumberFormats.
• Operation (optional) – specifies how the pasted data combines with existing data. Constants:
  xlPasteSpecialOperationAdd, xlPasteSpecialOperationDivide, xlPasteSpecialOperationMultiply, xlPasteSpecialOperationNone, xlPasteSpecialOperationSubtract.
• SkipBlanks (optional) – Boolean. If True, blanks in the source range are ignored.
• Transpose (optional) – Boolean. If True, transposes rows and columns.

Example 1: Add values from C1:C5 to existing values in D1:D5 on Sheet1:

With Worksheets("Sheet1")
    .Range("C1:C5").Copy
    .Range("D1:D5").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With

Example 2: Same as above, but paste starting at the top-left cell D1:

With Worksheets("Sheet1")
    .Range("C1:C5").Copy
    .Range("D1").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With

Example 3: Copy only values from A1:C1 and paste them into A5:C5:

Range("A1:C1").Copy
Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

Pasting a Range with Transposition

The PasteSpecial method allows you to paste a copied range with transposed orientation (rows become columns, and columns become rows) by setting the parameter Transpose = True.

Example: The following code copies the values from range A1:C2 and pastes them transposed into the range E1:F3:

Sub Transp()
    Range("A1:C2").Copy
    Range("E1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
        Transpose:=True
End Sub

Removing Selection After Paste Special

When data is copied or pasted into the clipboard, the source range remains highlighted, even after a PasteSpecial operation.
To clear this selection, set the Application.CutCopyMode property to False.

Example:

Range("C1:C5").Copy
Range("D1").PasteSpecial Operation:=xlPasteSpecialOperationAdd
Application.CutCopyMode = False

Inserting Cells, Rows, or Columns

The Insert method of the Range object adds a new cell, row, or column to a worksheet.

Insert(Shift, CopyOrigin)

• Shift (optional) – specifies how existing cells should be shifted. Possible values:
  • xlShiftToRight – shifts existing cells to the right.
  • xlShiftDown – shifts existing cells down.
• CopyOrigin (optional) – specifies how formatting and data sources should be copied.

Examples:

• Insert a new row before row 4:

Rows(4).Insert

• Insert a new cell to the left of G9, shifting existing cells to the right:

Range("G9").Insert Shift:=xlShiftToRight