Excel VBA – Range Object in Excel

The Range object is one of the most important objects in Excel VBA. It represents a single cell or a range of cells on a worksheet. The Range object has a wide range of properties and methods that can be used to manipulate cells and ranges.

Properties of the Range object

The function has many properties that can be used to get or set information about the range. Some of the most commonly used properties include:

  • Value: The value of the cell or range.
  • Formula: The formula for the cell or range.
  • Font: The font used for the cell or range.
  • Alignment: The alignment of the cell or range.
  • Border: The border style for the cell or range.

Methods of the Range object

The Range object has many methods that can be used to manipulate cells and ranges. Some of the most commonly used methods include:

  • Select: Selects the cell or range.
  • Copy: Copies the cell or range to the clipboard.
  • Paste: Pastes the cell or range from the clipboard.
  • Fill: Fills the cell or range with a value or formula.
  • Format: Formats the cell or range.

Using the Range object in Excel VBA

The function can be used in Excel VBA to automate tasks such as:

  • Copying and pasting data.
  • Formatting cells.
  • Calculating formulas.
  • Sorting and filtering data.
  • Creating charts and graphs.
See also  Excel Functions - Cell Reference in Excel

The Range object is a powerful tool that can be used to automate many tasks in Excel VBA. If you are new to Excel VBA, I recommend that you learn more about the Range object. There are many resources available online and in books that can help you learn about the Range object and how to use it in Excel VBA.

Range Object Examples

Place a command button on your worksheet and add the following code line:

Range(“B3”).Value = 2

Result when you click the command button on the sheet:

Excel VBA - Range Object Example

Code:

Range(“A1:A4”).Value = 5

Result:

Code:

Range(“A1:A2,B3:C4”).Value = 10

Result:

Note: to refer to a named range in your Excel VBA code, use a code line like this:

Range(“Prices”).Value = 15

Cells

Instead of Range, you can also use Cells. Using Cells is particularly useful when you want to loop through ranges.

Code:

Cells(3, 2).Value = 2

Result:

- Advertisement -

Example

Explanation: Excel VBA enters the value 2 into the cell at the intersection of row 3 and column 2.

Code:

Range(Cells(1, 1), Cells(4, 1)).Value = 5

Result:

Excel VBA - Range Object Cell Example

Declare a Range Object

You can declare a Range object by using the keywords Dim and Set.

Code:

Dim example As Range Set example = Range(“A1:C4”) example.Value = 8

Result:

Excel VBA - Declare Range Object

Select

An important method of the Range object is the Select method. The Select method simply selects a range.

Code:

Dim example As Range Set example = Range(“A1:C4”) example.Select

Result:

Excel VBA - Range Object: Select Method

Note: to select cells on a different worksheet, you have to activate this sheet first. For example, the following code lines select cell B7 on the third worksheet from the left.

Worksheets(3).Activate Worksheets(3).Range(“B7”).Select

Rows

The Rows property gives access to a specific row of a range.

See also  Excel VBA - Array in Excel

Code:

Dim example As Range Set example = Range(“A1:C4”) example.Rows(3).Select

Result:

Rows Property

Note: border for illustration only.

Columns

The Columns property gives access to a specific column of a range.

Code:

Dim example As Range Set example = Range(“A1:C4”) example.Columns(2).Select

Result:

Excel VBA - Range Object: Columns Property

Note: border for illustration only.

Copy/Paste

The Copy and Paste method are used to copy a range and to paste it somewhere else on the worksheet.

Code:

Range(“A1:A2”).Select Selection.Copy Range(“C3”).Select ActiveSheet.Paste

Result:

Excel VBA - Range Object: Copy Paste Method

Although this is allowed in Excel VBA, it is much better to use the code line below which does exactly the same.

Range(“C3:C4”).Value = Range(“A1:A2”).Value

Clear

To clear the content of an Excel range, you can use the ClearContents method.

Range(“A1”).ClearContents

or simply use:

Range(“A1”).Value = “

Note: use the Clear method to clear the content and format of a range. Use the Clear Formats method to clear the format only.

Count

With the Count property, you can count the number of cells, rows and columns of a range.

Excel VBA - Range Object: Count Property

Note: border for illustration only.

Code:

Dim example As Range Set example = Range(“A1:C4”) MsgBox example.Count

Result:

Excel VBA - Range Object: Count Cells

Code:

Dim example As Range Set example = Range(“A1:C4”) MsgBox example.Rows.Count

Result:

Excel VBA - Range Object: Count Rows

Note: in a similar way, you can count the number of columns of a range.

Next Chapter: Variables

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Stay in touch to boost your skills in Excel, HTML, and JavaScript! Get tips, tutorials, and practical examples to make your learning journey efficient, fun, and highly rewarding.

Related Articles