Range Object

Dynamic Range

This command works as follows. Each time you add a number and run the Dynamic Range code, it colors these numbers maximum value.

The code to find the maximum value of a currentRange of A2 is:

1. First, declare one variable and two Range objects. Declare variable maximum as double, rng, and cell as Range objects.

Dim maximum As Double, rng As Range, cell As Range

2. Change the background color of all cells to 'No Fill.'

Cells.Interior.ColorIndex = 0

3. Initialize rng with some numbers range. For this, we use the CurrentRegion property as follows:

Set rng = Range("A1").CurrentRegion

4. Initialize maximum with the maximum value of the numbers. Use the worksheet function, Max, to find the maximum value.

maximum = WorksheetFunction.Max(rng)

5. Next, we color the highest value. Use For Each Loop.

For Each cell In rng
    If cell.Value = maximum Then cell.Interior.ColorIndex = 22
Next cell

Note: You can use any ColorIndex number. Here ColorIndex number 27 (yellow) is used.

6. Run the code, and you can see that six is highlighted.

Resize

In Excel VBA, the Resize property increases or decreases the size of a range of rows and columns. The top-left cell of a range is always used as the starting point for the Resize property.

The following code is used to resize.

Range("A1:C4").Resize(3, 2).Select

Range("A1:C4") is resized to 3 rows and 2 columns in this code line, and the updated range is selected.

Entire Rows and Columns

It helps you to select entire rows and columns in Excel VBA. 

1. The following code selects the entire sheet.

Cells.Select

2. The following code selects the third column.

Columns(3).Select

3. The following code selects the fifth row.

Rows(5).Select

4. To select multiple rows, use the following code:

Rows("2:7").Select

Similarly, use Columns instead of rows to select multiple columns.

5. Select a cell. The following code selects the entire row of the active cell.

ActiveCell.EntireRow.Select

Offset

In Excel VBA, the Offset property selects a range away from another range by a given number of rows and columns.

The following code is used:

Dim example As Range
Set example = Range("A1:A2")
example.Offset(4, 3).Select

Explanation: These code lines choose the Range four rows and three columns below and to the right of the Range ("A1:A2"). The top-left cell of a range is always the beginning point for the Offset property.

From Active Cell to Last Entry 

This property selects the range from the Active Cell to the last entry in a column.

1. Add the following code with a particular cell. For, e.g., for A3:

Range("A3").End(xlDown).Select

 

Note: Instead of Range("A3"), you can also use Range("A1"), Range("A2"), etc. 

1. Add the following code to select the range from cell A4 to the last element in the column:

Range(Range("A4"), Range("A4").End(xlDown)).Select

1. Simply replace Range("A4") with ActiveCell to select the range from the Active Cell to the last entry in the column.

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Union and Intersect 

Union

In Excel VBA, the Union method returns a Range object representing the union of two or more ranges.

Use the following code:

Union(Range("B1:C5"), Range("C4:F5")).Select

Note: The Union property does not return the mathematical union (cell C6 and cell C7 are included twice).

Intersection

In Excel VBA, the Intersect method returns a Range object representing the intersection of two or more ranges.

Use the following code:

Intersect(Range("B1:C5"), Range("C4:F5")).Select

Test a Selection

This program uses the Count property, IsNumeric function, IsEmpty function, and Intersect method to test a selection.

Add the following code:

1. Begin by defining two Range objects. The Range objects are referred to as rng and cell.

Dim rng As Range, cell As Range

2. The Range object rng is initialized with Range("D5:E10"), and the Range object cell is initialized with the selected range.

Set rng = Range("D5:E10")
Set cell = Selection

3. Add the following code:

If cell.Count = 1 And IsNumeric(cell) And Not IsEmpty(cell) And Not Intersect(rng, cell) Is Nothing Then
    MsgBox "You win"
Else
    MsgBox "You lose"
End If

Explanation: The intersection of the two ranges is represented by the Range object returned by Intersect(rng, cell). The single number (first three conditions) is in the bounded range if this range object is not Nothing.

Run:

Only when you select a single number from the bordered range will you get the following result:

In all other cases, the result will be as follows:

Possible Football Matches

This property shows a print preview of all probable football matches based on a team list.

1. We start by declaring a single Range object as well as four variables. The Range object is represented as rng. Matchname is a String variable, and counter, i, and j are three Integer variables.

Dim rng As Range, matchname As String, counter As Integer, i As Integer, j As Integer

2. The team names are used to initialize rng. We don't know the exact range, so we use CurrentRegion. Initialize counter as 0.

Set rng = Range("A2").CurrentRegion
counter = 0

3. Column C is where we write all of the possible football matchups. First, we'll empty column C.

Worksheets(1).Columns(3) = ""

4. Start a double loop.

For i = 1 To rng.Count
    For j = i + 1 To rng.Count

5. Write a match name to the variable matchname.

matchname = rng.Cells(i).Value & " vs " & rng.Cells(j).Value

6. Write the above matchname to column C.

Cells(counter + 1, 3).Value = matchname

7. The counter counts how many matchnames have been written to column C. Each time Excel VBA writes a matchname to column C, and it increases the counter by one. Add the following code:

counter = counter + 1
Next j
Next i

8. Show output using:

ActiveSheet.Columns(3).PrintPreview

Output:

Font 

In Excel VBA, the Font object has many properties, including the Color and Bold properties. Let’s have a look.

Color property

Use the Range object's Font property and then the Color property of the Font object to change the color of an Excel range.

Range("A2").Font.Color = -16776961

OR

Range("A2").Font.Color = vbRed

OR

Range("A2").Font.Color = RGB(255, 0, 0)

Bold property

Range("A2").Font.Bold = True

Background Colors

It's simple to change the backdrop colors in Excel VBA. To get an Interior object, use the Interior property. Then use the ColorIndex property of the Interior object to set the background color of a cell.

The code below changes cell A1's background color to blue.

Range("A1").Interior.ColorIndex = 23

The code below changes the cell A1's background color  to 'No Fill.'

Range("A1").Interior.ColorIndex = 0.

You can simply ask Excel VBA for the ColorIndex number of a color.

MsgBox Selection.Interior.ColorIndex

Areas Collection

We can border Ranges under this property. For example, ("B2:C3,C5:E5"). This range has two areas. The comma separates the two areas.

1. We begin by declaring two Range objects. rangeToUse and singleArea are the Range objects.

Dim rangeToUse As Range, singleArea As Range

2. Initialize rangeToUse with Range("B2:C3,C5:E5").

Set rangeToUse = Range("B2:C3,C5:E5")

3. Add the following code to count the number of areas in rangeToUse:

MsgBox rangeToUse.Areas.Count

4. The index values can refer to the various areas of rangeToUse. The next line of code counts how many cells are in the first area.

Note: You can also count the number of cells in each area of rangeToUse by looping through them. 

Compare Ranges

This property of Range Objects compares randomly selected ranges and highlights the unique cells. 

1. First, we create four Range objects and two Integer variables.

Dim rangeToUse As Range, singleArea As Range, cell1 As Range, cell2 As Range, i As Integer, j As Integer

2. With the selected range, we initialize the Range object rangeToUse.

Set rangeToUse = Selection

3. Add the line that makes all of the cells' backgrounds 'No Fill' and removes all cell borders.

Cells.Interior.ColorIndex = 0
Cells.Borders.LineStyle = xlNone

4. When the user only selects one area, inform him or her.

If Selection.Areas.Count <= 1 Then
      MsgBox "Please select more than one area.

5. Else add Color to the cells of the selected areas.

Else
rangeToUse.Interior.ColorIndex = 38

6. Add Border to each of the areas.

For Each singleArea In rangeToUse.Areas
    singleArea.BorderAround ColorIndex:=1, Weight:=xlThin
Next singleArea

1. Add the main functionality

For i = 1 To rangeToUse.Areas.Count
    For j = i + 1 To rangeToUse.Areas.Count
        For Each cell1 In rangeToUse.Areas(i)
            For Each cell2 In rangeToUse.Areas(j)
                If cell1.Value = cell2.Value Then
                    cell1.Interior.ColorIndex = 0
                    cell2.Interior.ColorIndex = 0
                End If
            Next cell2
        Next cell1
    Next j
Next i
End If

Output:

Frequently Asked Questions

1. What is the range object?
   The VBA Range Object represents a cell or multiple cells in your Excel worksheet. It is Excel VBA's most significant object. You can use the Excel VBA range object to refer to a single cell, a set of cells in a row or column.

Conclusion

In this article, we learned about the Range Object in Excel. The Range object is the most significant object in Excel VBA since it represents cells on your worksheet. 

We learned about the following properties and functions of the Range objects.

• CurrentRegion
• Dynamic Range
• Resize
• Entire Rows and Columns
• Offset
• From Active Cell to Last Entry
• Union and Intersect
• Test a Selection
• Possible Football Matches
• Font
• Background Colors
• Areas Collection
• Compare Ranges

You are all set to play with cells, rows, and columns.

Happy Learning!

You can use Coding Ninjas Studio to practice various DSA questions asked in the interviews. It will help you in mastering effective coding techniques, and you will also get interview experiences with people working in big companies.