How to Use the Range Property in Excel VBA (the Ultimate Guide)

1. Using Range Address

To represent a single cell or a range of cells, you can use the Range Object, where the argument names the range. Here is the syntax to specify a range:

To specify a range that has one cell, cell B6, enter:

Range ("B6")

Referring to a range of cells, for example, range B6 to E16, there are two acceptable ways:

Range("B6:E16")

and

Range("B6", "E16")

Note:

The above syntaxes use double quotations (” “) to enclose the range address. Alternatively, you can type quickly using the square brackets without quotation marks. For example:

You may use [B6:E16] instead of Range(“B6:E16”) to refer to the range B6:E16.

2. Using Cells Property

You can use the Cells property to refer to a range in VBA. Using the Cells property is useful while looping through rows or columns. The Cells property takes two arguments:

Cells (Row, Column)

For the Row arguments, you only need to use numeric values. You may use either a string or numeric value for Column arguments. In the below example, both code lines refer to A1:E5:

Range(Cells(1,1),Cells(5,5))

Range (Cells(1,”A”),Cells(5,”E”))

Note:

To refer to a single cell, you can simply use the lines below without mentioning the Range object.

Cells(6, 2) or Cells(6, “B”)

3. Referring to Entire Rows as Range

Use the Range object in VBA to refer to an entire row or multiple rows.

Here are the syntaxes for both single and multiple rows.

For single row range:

Range("6:6")

For multi-row range:

Range("6:8")

4. Setting Entire Columns as Range

To set an entire column or multiple columns, use the following syntaxes.

For single-column range:

Range ("B:B")

For multiple column range:

Range ("B:E")

5. Setting Non-Contagious Cells as Range

To set non-contagious cells as a range in VBA, use Comma (,) to separate each area. For referring to range B6:C6 and E6:E16 at once, you can use this line:

Range("B6:C16, E6:E16")

6. Using the Resize Property to Set Range

The Resize property in Excel VBA changes the size of a range.

Your current range is B6, and you want to resize it to B6:E9. Therefore, the syntax of the Range object in VBA is-

Range("B6").Resize(4, 4)

How to Set a Dynamic Range in Excel VBA

In the previous examples, you specified a fixed range size when using the VBA Range object. However, you often don’t know the exact range size when dealing with variable ranges. In such situations, you need dynamic ranges in Excel VBA. Here are a few ways to set a dynamic range in VBA.

1. Setting Range Variable to Selection

You can set range variables to selection in Excel VBA and then use the variables to access properties and methods. Consider the below dataset where the range B6:E9 is selected:

Using the following code to change the cell color of the selected region as range variables:

Sub SettingRangeVariabletoSelection()
'Developed by ExcelDemy
Dim rng As Range
Set rng = Selection
rng.Interior.Color = RGB(245, 220, 202)
End Sub

Thus, the interior color of the selected range changes.

2. Using Input Box to Select Range

In this method, you will use an input box to select a range and change its color. Consider the previous dataset and run the below VBA code:

Sub UsingInputBoxtoSelectRange()
'Developed by ExcelDemy
    Set rng = Application.InputBox("Select a range:", Type:=8)
    rng.Interior.Color = RGB(245, 220, 220)
End Sub

Enter the desired range in the message box and select OK. Here, we input range B6:E9.

As a result, the interior color of the desired range changes.

3. Applying UsedRange Property

Consider a dataset where you constantly enter values and do not know how big it could get, you can use the UsedRange property. The following VBA code will select the used region:

Sub ApplyingUsedRangeProperties()
'Developed by ExcelDemy
ActiveSheet.UsedRange.Select
End Sub

Running the VBA code selects the whole used region.

4. Using CurrentRegion Property

This CurrentRegion property can cover all the contagious cells of the active cell region. Here is the VBA code to select the contagious cells of B6:

Sub UsingCurrentRegionProperties()
'Developed by ExcelDemy
Range("B6").CurrentRegion.Select
End Sub

The current region of cell B6 gets selected.

5. Setting Range with Variable Row and Column

You want to set a range with variable row and column numbers. To do this, specify the first row and column with variables and use the End property to get the last row and column. The End property for the Range object refers to the last cell within the range. Consider the following VBA code:

Sub SettingVariableRowColumn()
'Developed by ExcelDemy
    first_row = 5
    first_col = 2 ' or "B"
    last_row = Cells(Rows.Count, first_col).End(xlUp).Row
    last_col = Cells(first_row, Columns.Count).End(xlToLeft).Column
    Range(Cells(first_row, first_col), Cells(last_row, last_col)).Select
End Sub

Here, we have assigned the first row and first column as 5 and 2, which indicates cell B5. Then, we can assign the last rows and columns using the End property. After that, we selected the region by setting the range with variable row and column numbers.

How to Set a Range Using Named Range in Excel VBA

You can set a range using Named Range in Excel VBA by enclosing the range name with double-quotations in the Range object argument. Let’s assume you have a Named Range named myRng that represents range B6:E16.

In the following VBA code, set the range using the named range:

Sub SettingRangeUsingNamedRange()
'Developed by ExcelDemy
    Range("myRng").Select
End Sub

As a result, the Named Range gets selected.

How to Select Range with VBA in Excel

You can select a range using the Select method of the Range object in VBA. In the previous sections, you have learned how to specify a range. Here, you will see some practical examples of selecting ranges with VBA.

1. Selecting a Single Cell

Let’s consider the dataset that represents the sales data of some brands.

To select a single cell B6, run the below VBA code:

Sub SelectingaSingleCell()
'Developed by ExcelDemy
    Range("B6").Select
End Sub

Running the code selects the cell B6.

2. Selecting Entire Rows

To select the entire rows 6 to 8, use the following VBA code:

Sub SelectingEntireRows()
'Developed by ExcelDemy
Range("6:8").Select
End Sub

3. Selecting Entire Columns

Select the entire columns D & E using the following VBA code:

Sub SelectingEntireColumn()
'Developed by ExcelDemy
    Range("D:E").Select
End Sub

4. Selecting a Contagious Range

You can also select all the contagious cells at once. Consider the below VBA code:

Sub SelectingContagiousRange()
'Developed by ExcelDemy
    Range("B6", "E16").Select
End Sub

The code selects every contagious cell of B6:E16.

5. Selecting a Non-Contagious Range

Select the non-contagious cells of the same dataset. Use the below VBA code:

Sub SelectingNonContagiousCells()
'Developed by ExcelDemy
Range("B6:C16, E6:E16").Select
End Sub

After running the code, two mentioned areas in the code were selected.

6. Selecting a Used Range

Select the used range of your active sheet using the Used Range property in VBA. Use the following VBA code:

Sub SettingUsedRangeinColumn()
'Developed by ExcelDemy
ActiveSheet.UsedRange.Select
End Sub

Select the entire range of used items on the active sheet.

7. Selecting the Last Cell in a Range

If you want to select the last cell of a large column without scrolling, you can use the below VBA code:

Sub SelectingLastCellInRange()
Range("B6").End(xlDown).Select
End Sub

The last cell of the column B is selected.

8. Selecting a Range from the Active Cell to the End

Let’s say you have a selected cell in the dataset.

You can select range from the active cell in Excel with VBA Range Object to the end. Here is the VBA code:

Sub SelectfromActiveCelltoEnd()
'Developed by ExcelDemy
Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Select
End Sub

The code selects the range up to the last cells.

9. Stopping on Multi-Select Using VBA in Excel

Using Range Object in VBA, you can stop an operation when you have multiple selections in your data. For Example, in the dataset, we have multiple selections of different areas.

The following VBA code stops any operations on multiple selections:

Sub StoppingOnMultiSelection()
'Developed by ExcelDemy
    selected_area_count = Selection.Areas.Count
    If selected_area_count > 1 Then
    MsgBox "Cannot carry out the task on multiple selections"
    End If
End Sub

As a result, the code stops any activity on multiple selections.

How to Apply Offset Property of Range in Excel VBA

You can apply the active cell Offset Property in the VBA Range object to offset a range selection. The Offset property shifts the position of the range, and the Resize property enlarges the size of the range to the specified range. Let’s say you have an active cell B5 in your dataset.

The following VBA code offsets your selection to one row and one column and resizes the selection to cover 11 rows and 1 column:

Sub ApplyOffsetProperties()
'Developed by ExcelDemy
    ActiveCell.Offset(1, 1).Resize(11, 1).Select
End Sub

Running the code results in the range B8:B16 being selected.

How to Loop Through a Range in Excel VBA

1. Looping Through Each Cell in a Range

This procedure shows how VBA For Each loops through each cell in range C6:D15. In this example, you will loop through the product’s unit price and quantity of the specified range and calculate the tax in the corresponding E column.

The VBA code calculates the tax values within the loop and enters them in E6:E15. Here is the VBA code:

Sub LoopingForEachCell()
'Developed by ExcelDemy
Set target_range = Range("E6:E15")
tax_value = 0.05
For Each cell In target_range
    cell.Value = cell.Offset(0, -2).Value * cell.Offset(0, -1).Value * tax_value
Next cell
End Sub

As a result, the tax values appear in E6:E15.

2. Looping Through Each Row in a Range

Now, we want VBA For Each loop to loop through each row 6 to 15. In the loop, we will look for odd or even rows and highlight the odd rows. Use the below VBA code:

Sub LoopingForEachRow()
'Developed by ExcelDemy
    Set working_range = Range("B6:E15")
    For i = 1 To working_range.Rows.Count
        If i Mod 2 = 0 Then
            working_range.Rows(i).Interior.Color = RGB(245, 220, 220)
        Else
            working_range.Rows(i).Interior.Color = xlNone
        End If
    Next i
End Sub

The code highlights the odd rows, and the even rows remain the same.

How to Work with VBA Range Properties in Excel

In this section, you will look closely at some useful Range properties in VBA. The Range Property represents cells, rows, and columns and manipulates cell formattings. Here are a few examples of Range Property in Excel VBA.

1. Changing Fill Color

Consider the below dataset where we want to change the fill color of the range B6:E16.

Sub ChangingFillColor()
'Developed by ExcelDemy
    Range("B6:E16").Interior.Color = RGB(245, 220, 220)
End Sub

The fill color of the mentioned range changes.

2. Changing Font Style, Size and Color

Similarly, you can change the font style, size, and color using the Range Property. Let’s change the formatting of range E6:E16. Use the following VBA code:

Sub ChangeFontStyle()
'Developed by ExcelDemy
    Dim myRange As Range
    Set myRange = Range("E6:E16")
    With myRange.Font
        .Size = 11
        .Bold = True
        .Italic = True
        .Color = RGB(220, 120, 140)
    End With
End Sub

The new formatting appears in the desired range.

3. Applying Border

If your sheet has borderless data, as shown below, you can apply borders using VBA with Range Property.

Use the following VBA code to apply borders:

Sub ApplyingBorders()
'Developed by ExcelDemy
    Dim myRange As Range
    Set myRange = Range("B5").CurrentRegion
    With myRange.Borders
        .LineStyle = xlContinuous
        .Color = RGB(208, 206, 206)
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Thus, you obtain the borders in the range.

4. Changing Alignment

You can customize the cell alignment using this property. To change the alignment of the range, use the following VBA code:

Sub ChangingAlignment()
'Developed by ExcelDemy
    Dim myRange As Range
    Set myRange = Range("B6:E16")
    With myRange
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
    End With
End Sub

The cell alignments of the range B6:E16 move vertically and horizontally to the center.

How to Work with VBA Range Methods in Excel

In VBA, methods are built-in functions that belong to specific objects and perform actions on them. The Range object in VBA provides various methods, each with its own specific purpose. Here are some of the most commonly used methods: Value, Clear, Copy, Delete, etc.

Copy-Pasting Range

Copy and paste a range with VBA using the Copy and Paste method with the Range object. See the below dataset where you will copy range B6:E16 of the Copy Paste Range worksheet.

Run the below VBA code:

Sub CopyPasteRange()
'Developed by ExcelDemy
    Sheets("Copy Paste Range").Activate
    Range("B5:E16").Select
    Selection.Copy
    Sheets("Target Range").Activate
    Range("B2").Select
    ActiveSheet.Paste
End Sub

After running the code, we get the copied value in the Target Range sheet.

Counting Total Cells in a Range

If you have a selected range as given below, you can count the total cells using the Count method of the VBA Range object.

Consider the below VBA code where we have used the Count method:

Sub CountTotalCellsInRange()
'Developed by ExcelDemy
    Set target_range = Selection
    MsgBox "Total Cells Count: " & target_range.Cells.Count
End Sub

The code will show you the total number of cells in a message box.

Clearing Contents of a Range

The Range object uses the ClearContents method to clear the contents in VBA from the specified range E6:E16 of the data below.

Use the following VBA code and run it.

Sub ClearContentsofRange()
'Developed by ExcelDemy
    Range("E6:E16").ClearContents
End Sub

As a result, the data of the specified range is cleared.

Autofill a Range

Let’s say you have calculated a tax value in E6 based on the unit price and quantity using the given formula in F6. Using the AutoFill method, you can autofill the range in VBA.

Here is the VBA code to autofill the range E6:E15:

Sub AutoFillRange()
'Developed by ExcelDemy
    Range("E6").AutoFill Range("E6:E15")
End Sub

Thus, you obtain the rest of the tax values.

Merging Cells in a Range

Imagine the scenario below, where the Brand column has similar values consecutively, and you can merge the cells with similar values for better visibility.

Use the Merge method in the VBA code provided below and execute it.

Sub MergeCells()
'Developed by ExcelDemy
Application.DisplayAlerts = False
    Range("B6:B9").Merge
    Range("B10:B13").Merge
    Range("B14:B16").Merge
Application.DisplayAlerts = True
End Sub

We obtain cells with similar data merged.

Filtering a Range Using Auto Filter

The AutoFilter method in the Range object can filter data based on criteria. In the below dataset, we will filter the range B5:E16 when the values of range E6:E16 are less than 800.

The following VBA code uses the .AutoFilter method to filter.

Sub FilteringRange()
'Developed by ExcelDemy
 Range("B5:E16").AutoFilter Field:=4, Criteria1:="<800"
End Sub

Running the code gets us the filtered range based on the specified conditions.

Using Advanced Filter Method

You can also use auto and Advanced filters in VBA to filter range B5:E16 based on the criteria in range G5:H6. The Advanced Filter method copies the filtered range and pastes it to a specified location.

Use the VBA code with the .AdvancedFilter method and run it.

Sub UsingAdvancedFiltertoRange()
' Developed by ExcelDemy
        Range("B5:E16").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Range("G5:H6"), _
        CopyToRange:=Range("G8")
End Sub

The filtered range appears after running the code.

Sorting a Range

In the given dataset, we will sort range using VBA based on the range E6:E16 in ascending order.

Use the given VBA code and run it.

Sub SortRange()
'Developed by ExcelDemy
    Dim rngToSort As Range
    Set rngToSort = Range("B5:E16")
    rngToSort.Sort key1:=rngToSort.Columns(4), _
                    order1:=xlAscending, _
                    Header:=xlYes
End Sub

Thus, we get the sorted range based on the prices.

Finding Unique Values in a Range

Let’s highlight the unique values from range E6:E16 using the Range object in the given example.

Here is the VBA code to highlight the unique value of the range:

Sub FindUniqueValues()
'Developed by ExcelDemy
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("E6:E16")
    rng.Interior.ColorIndex = xlNone
    For Each cell In rng
        If Application.WorksheetFunction.CountIf(rng, cell.Value) = 1 Then
            cell.Interior.Color = RGB(245, 220, 220)
        End If
    Next cell
End Sub

After running the code, it will highlight the unique value.

Removing Duplicates from a Range

Now, you will use the RemoveDuplicates method to remove duplicates in Excel VBA. See the range E6:E16 below, which has duplicate values 620.

Use the VBA code and run.

Sub RemoveDuplicates()
'Developed by ExcelDemy
ActiveSheet.Range("B6:E16").RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo
End Sub

As you can see, there was one duplicate value and its entire row disappeared.

Finding Any Value in a Range

Now, you will search for “Notebook” in range B6:E16 using the Find Method in Excel VBA Range Object. The Find method searches any specified value and returns a message box showing its location if found. The method will return the first location only if there are multiple locations. Use the following VBA to search:

Sub FindAnyValue()
'Developed by ExcelDemy
    Dim searchValue As Variant
    Dim searchRange As Range
    Dim foundCell As Range
    searchValue = "Notebook"
    Set searchRange = Range("B6:E16")
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
    If Not foundCell Is Nothing Then
        MsgBox "The Value is at cell " & foundCell.Address
    Else
        MsgBox "Value not found in the specified range."
    End If
End Sub

The code finds the value in C6.

Converting Text to Columns

You might need to convert text to columns in VBA for data visualization. See the dataset below, which lists full names in the range B6:B16. We will divide the full names into first and last names in separate columns using the TextToColumns method.

The VBA code divides the names and places them in columns C & D:

Sub ConvertTextToColumns()
'Developed by ExcelDemy
    Application.DisplayAlerts = False
    Dim rng As Range
    Set rng = Range("B6:B16")
    rng.TextToColumns Destination:=Range("C6"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierNone, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=True, _
        Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
        DecimalSeparator:=".", _
        ThousandsSeparator:=",", _
        TrailingMinusNumbers:=True
    Application.DisplayAlerts = True
End Sub

The code converts the text into columns and returns the first and last names.

Calculating Range

Lastly, you can calculate a range in VBA using the Calculate method. The Calculate method allows you to compute values across all open workbooks, specific workbooks, targeted worksheets, ranges, columns, or rows. If you have a formula range, the Calculate method can recalculate or refresh that range. The following VBA recalculates the range.

Sub CalculateRange()
'Developed by ExcelDemy
Range("E6:E15").Calculate
MsgBox "The range has been recalculated!", vbInformation
End Sub

After running the code, the specified range will be recalculated.

How to Convert Array to Range or Range to Array Using VBA in Excel

In this section, you will convert an array to a range and range to an array using the Range object in Excel VBA.

1. Convert Array to Range

If you have an array, insert it in the VBA code and place it in range E6:E16 of the given dataset.

The VBA code uses the Range object to specify the range and uses the range to hold the array.

Sub ConvertArrayToRange()
'Developed by ExcelDemy
    Dim myArray As Variant
    Dim myRange As Range
    myArray = Array(400, 500, 550, 600, 650, 500, 700, 730, 780, 980, 1000)
   Set myRange = Range("E6").Resize(UBound(myArray) + 1, 1)
    myRange.Value = Application.Transpose(myArray)
End Sub

Hence, you find the array stored in the specified range.

2. Convert Range to Array

Alternatively, you can use the same range and convert them into arrays using the Range object. The below VBA code will do the task using a message box.

Sub ConvertRangeToArray()
'Developed by ExcelDemy
    Dim myRange As Range
    Dim dataArray As Variant
    Dim i As Long, j As Long
    Dim resultString As String
    Set myRange = Range("E6:E16")
    dataArray = myRange.Value
    For i = LBound(dataArray, 1) To UBound(dataArray, 1)
        For j = LBound(dataArray, 2) To UBound(dataArray, 2)
            resultString = resultString & "Value at (" & i & ", " & j & "): " & dataArray(i, j) & vbCrLf
        Next j
    Next i
    MsgBox resultString, vbInformation, "Array Values"
End Sub

The above procedure returns a message box showing the array in the message box.

How to Reference Range from Another Sheet with Excel VBA

To reference a range from another sheet, use the Range object to specify the range in VBA. Here is the VBA code for referring to a range E6:E16 from the Calculate Range sheet.

Sub ReferRangefromAnotherSheet()
'Developed by ExcelDemy
    Dim myRange As Range
    Set myRange = Worksheets("Calculate Range").Range("E6:E16")
    Dim rangeValues As Variant
    rangeValues = myRange.Value
    Dim resultString As String
    Dim i As Long, j As Long
    For i = LBound(rangeValues, 1) To UBound(rangeValues, 1)
        For j = LBound(rangeValues, 2) To UBound(rangeValues, 2)
            resultString = resultString & rangeValues(i, j) & vbTab
        Next j
        resultString = resultString & vbCrLf
    Next i
    MsgBox resultString, vbInformation, "Range Items"
End Sub

After running the code, you will get a message box displaying the specified range from the worksheet.

How to Reference Range from Another Workbook with Excel VBA

The below procedure will specify a range from another workbook using the Range object in Excel VBA.

Specify your file location in the VBA code and run it.

Sub ReferRangeFromAnotherWorkbook()
'Developed by ExcelDemy
    Dim otherWorkbook As Workbook
    Dim myRange As Range
    Dim otherWorkbookPath As String
    otherWorkbookPath = "C:\Users\YOUSUF\Desktop\Target File.xlsm"
    Set otherWorkbook = Workbooks.Open(otherWorkbookPath)
    Set myRange = otherWorkbook.Worksheets("Calculate Range").Range("E6:E16")
    Dim rangeValues As Variant
    rangeValues = myRange.Value
    Dim resultString As String
    Dim i As Long, j As Long
    For i = LBound(rangeValues, 1) To UBound(rangeValues, 1)
        For j = LBound(rangeValues, 2) To UBound(rangeValues, 2)
            resultString = resultString & rangeValues(i, j) & vbTab
        Next j
        resultString = resultString & vbCrLf
    Next i
    otherWorkbook.Close SaveChanges:=False
    MsgBox resultString, vbInformation, "Range Items"
End Sub

In this way, you can access a range of other workbooks.

What to Do When Macro Returns Subscript Out of Range Error in Excel VBA?

The Subscript Out of Range error can occur for mainly these reasons:

Trying to access a cell beyond the range’s actual size.
Using properties or methods of a non-existent Range object.
Accidentally deleting definitions in a Named Range.
The defined worksheet in VBA isn’t open or accessible on the user’s computer.
The worksheet or the range is nonexistent.
The provided worksheet name doesn’t match the actual name of the desired sheet, etc.

In the scenario below, the Range object could not find any Fixed range. As a result, it shows the Run-time error ‘9’: Subscript out of range error.

To fix the situation, the proper way is to put the correct sheet name in the code. You can add an error handler like the VBA code below to avoid this error.

Sub FixSubscriptOutOfRangeError()
'Developed by ExcelDemy
    On Error Resume Next
     Worksheets("Fixed").Activate
    If Err.Number <> 0 Then
        MsgBox "Worksheet 'Fixed' not found.", vbExclamation
        Err.Clear
        Exit Sub
    End If
    On Error Resume Next
    Range("E6:E16").Select
    If Err.Number <> 0 Then
        MsgBox "Range [E6:E16] not found on the 'Fixed' worksheet.", vbExclamation
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0
End Sub

Running the code returns an error message box saying the worksheet is not found.

Download the Practice Workbook