Selection Property VBA – Usage With Examples

Today we will learn an important property to work with while using VBA in Excel. The Selection Property in VBA is very easy to learn. We will learn to use the Selection property with the help of examples.

So let us begin learning.

Selection Property in VBA

The Selection Property in VBA returns the Selected Object. It can be a Range, Chart, Picture, or Nothing. Nothing is returned when we haven’t selected anything. We can perform various operations on the Selected range of cells with the help of the Selection Property.

Most of the things we can perform using the Range Object, we can do it with the Selection property as it also returns the selected Range of Cells Object.

Selecting Range of Cells – Select Method

To select a range of cells with the help of VBA, we can use the Select method with the Range.

Select Range A1:D4 in the active sheet using the following Sub Procedure.

Sub selectRange()
'select a range of cells
Range("A1:D4").Select
End Sub

When you run this Sub procedure, it selects the range A1:D4 in the active sheet.

If you wish to select the range of cells in a worksheet other than the active sheet, you need to activate it first.

Let us say the active sheet is Sheet2 and we need to select the range A1:A6 of Sheet1. You will use the following code to select a range of cells in different worksheets.

Sub selectRangeDifferentSheet()
'select a range of cells in worksheet other than active sheet
'activate the required worksheet
Workbooks(2).Worksheets("Sheet1").Activate
'select the range
Range("A1:A6").Select
End Sub

Change Value in Selected Range of Cells

We can change the value in a selected range of cells with the help of the Selection property. For instance, the following code will write “ExcelUnlocked” in all cells in the selected range.

First, we select the range B2:D4 and then use the Selection property.

Sub ChangeSelectionValue()
'select the range
Range("B2:D4").Select
'change the value
Selection.Value = "ExcelUnlocked"
'change the interior color of selected range of cells
Selection.Interior.Color = RGB(204, 255, 229)
End Sub

Auto Fit Column Width for Selected Range

You can see that we have adjusted the column width to fit the contents. You can do this with the help of AutoFit method of the columns in a selected range of cells.

Selection.EntireColumn.AutoFit

Use the following code that will also Auto Fit the Column Width of the Selected Range of Cells.

Sub ChangeSelectionValue()
'select the range
Range("B2:D4").Select
'change the value
Selection.Value = "ExcelUnlocked"
'change the interior color of selected range of cells
Selection.Interior.Color = RGB(204, 255, 229)
'change the column width
Selection.EntireColumn.AutoFit
End Sub

Clear Contents of Selected Range

You can use the Clear method with the Selection property to clear the contents in the cells of the selected range.

Selection.Clear

Change the Font Color and Back Color of the Selected Range

You can use the Interior. Color property to change the back color of selected cells and font.color property to change the font color of the selected cells.

Use the following code to apply font properties to the selected range.

Sub ChangeFontProperties()
'apply font properties
'select the range
Range("A1:C3").Select
'change the value in range
Selection.Value="Excel"
'change font color
Selection.Font.Color=RGB(102,102,255)
'change back color
Selection.Interior.Color=RGB(204,229,255)
End Sub

Set Selected Range to Range Object

You can use a Range Object to refer to the currently selected Range. This will save the reference to the selected range in the new Range Object. You will be able to select another range and work with the previously selected range referred to by Range Object.

In the below code, we first select the range A1:C2 and then set it to myRange object. Thereafter we can work with the range using the myRange object.

Sub SetSelectionToObject()
'set selected range to object
'declare a range object
Dim myRange As Range
'select a range
Range("A1:C2").Select
'set the selected range to object
Set myRange = Selection
'change the back color of myRange using Loop
myRange.Interior.Color = RGB(255, 102, 102)
End Sub

You can also use the OFFSET with the Selection property.

This brings us to an end.

Thank you for reading. ❤