Refer to Excel Cells Using Range Object

We learn how to refer to Excel worksheet cells by using a range object in a macro in Excel and then perform actions on it.  If you set an object variable to a Range object, you can easily manipulate the Excel range by using the variable name in Microsoft Excel.  The following procedure creates the object variable RandomNums and then assigns the variable to Excel range B2:B6 on Sheet1 in the active Excel workbook. Subsequent statements modify properties of the Excel range by substituting the variable name for the range object.

Sub UsingRangeObject()

Dim RandomNums As Range

Set RandomNums = Worksheets(“Sheet1”).Range(“B2:B6”)

RandomNums.Formula = “=RAND()”

RandomNums.Font.ColorIndex = 3

End Sub

The Excel  training video below shows how to create and execute the Excel macro.

Implementing the macro:

  1. Click on Tools in the menu bar, select Macro and from the drop-down list select Visual Basic Editor
  2. In the Visual Basic editor window click on the menu Insert and select module
  3. First define the name  UsingRangeObject()
  4. Next use Dim, short for dimension, to set the data type for the variable RandomNums
  5. Now assign RandomNums to a specific Excel worksheet range like B2:B6
  6. The next line generates random numbers in the defined range of cells using a formula
  7. The second last line formats the font of the generated random numbers in  RandomNums to red color 8.Press F5 or the run button ‘>’ to see the macro in action

Further reading:

Referring To Ranges In Your VBA Code

Leave a Reply

Your email address will not be published. Required fields are marked *