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.
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.
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. ❤