How to use Range Variable in VBA Excel

In VBA (Visual Basic for Applications), a range variable refers to a variable that represents a cell, a range of cells, or an object within the Excel application. It is commonly used to manipulate and perform operations on specific cells or ranges in an Excel worksheet. The range variable allows you to interact with Excel’s data more dynamically and flexibly. It enables you to perform various tasks, such as reading values from cells, writing values to cells, formatting cells, calculating formulas, and much more.

Method 1: Using Range Variables in VBA to Copy a Range

Step 1 – Utilize Developer Tab

• Click on the Developer Tab in the ribbon 
• Then select the option of Visual Basic in the Developer Tab and a window will appear
• From the Window click on the Insert tab, and a drop-down menu will appear
• Click on Module from this menu and a Module window will appear

Step 2 – Type VBA Code

• Now type the VBA code in the window

“Sub vba_range_variable()

  Dim rng As Range

  Set rng = Range(“A1:B5”)

  rng.Copy

  End Sub”

• The subroutine is called “vba_range_variable”.
• Declares a range variable named “rng“.
• Sets the “rng” variable to represent the range of cells A1 to B5 in the currently active worksheet using the Range(“A1:B5”) syntax.
• Calls the Copy method on the “rng” range object to copy its contents to the clipboard

Step 3 – Click on Macros

• After typing the code go to the worksheet 
• Click on the Macros option in the Code group of the Developer tab and a Macros dialog box will appear 
• Now choose the Module that you have created and then click on the Run
• After clicking on the run, the Range will be copied

Method 2: Using Range Variables in VBA to Find Maximum Value in a Range

Step 1 – Utilize Developer Tab

• Click on the Developer Tab in the ribbon 
• Then select the option of Visual Basic in the Developer Tab and a window will appear
• From the Window click on the Insert tab, and a drop-down menu will appear
• Click on Module from this menu and a Module window will appear

Step 2 – Type the VBA Code

• Now type the VBA code in the window

“Sub vba_range_variable()

  Dim iNames As Variant

  Dim rng As Range

  Set rng = Range(“A2:B6”)

  Range(“C2”) = WorksheetFunction.Max(rng)

  End Sub”

• Dim iNames As Variant: This line declares a variable named iNames as a Variant data type. However, this variable is not used in the code you provided.
• Dim rng As Range: This line declares a variable named rng as a Range data type. This variable will be used to store a reference to a range of cells.
• Set rng = Range(“A2:B6”): This line assigns the range “A2:B6” to the variable rng. It uses the Range function to specify the range of cells from cell A2 to cell B6.
• Range(“C2”) = WorksheetFunction.Max(rng): This line sets the value of cell C2 to the maximum value within the range specified by the variable rng. It uses the “Worksheet Function”.Max function to calculate the maximum value.

Step 3 – Click on Macros

• After typing the code go to the worksheet 
• Click on the Macros option in the Code group of the Developer tab and a Macros dialog box will appear 
• Now choose the Module that you have created and then click on the Run
• After clicking on the run, the maximum value will appear 

Method 3: Using Range Variable VBA to Count Rows and Columns in the Range

Step 1 – Utilize Developer Tab

• Click on the Developer Tab in the ribbon 
• Then select the option of Visual Basic in the Developer Tab and a window will appear
• From the Window click on the Insert tab, and a drop-down menu will appear
• Click on Module from this menu and a Module window will appear

Step 2 – Type the VBA Code

• Now type the VBA code in the window

“ Sub vba_range_variable()

   Dim rng As Range

   Set rng = Range(“A1:B5”)

   MsgBox “This range has ” & _

   rng.Rows.Count & ” row(s) and ” & _

  rng.Columns.Count & ” column(s).”

  End Sub ”

• Dim rng As Range: This line declares a variable named rng as a Range data type. This variable will be used to store a reference to a range of cells.
• Set rng = Range(“A1:B5”): This line assigns the range “A1:B5” to the variable rng. It uses the Range function to specify the range of cells from cell A1 to cell B5.
• MsgBox “This range has ” & rng.Rows.Count & ” row(s) and ” & rng.Columns.Count & ” column(s).“: This line displays a message box containing information about the range rng. It uses the MsgBox function to show a message and concatenates the number of rows and columns in the range using the Rows. Count and Columns. Count properties of the rng object

Step 3 – Click on the Run Button

• Now click on the Run button in the ribbon
• After clicking on the Run button a dialog box will appear and give