Nahian
In this article, we will demonstrate some ideas about how to use Excel VBA to set a range variable to selection. We can execute some common operations on these selected cells using VBA. In the following Excel dataset, we are showing some western band names and their corresponding vocal singers.
Download Practice Workbook
5 Ways to Use VBA to Set a Range Variable to Selection in Excel
1. Setting Range Variable to Select a Range by Excel VBAÂ
We can select a range by setting range in VBA. Suppose we want to select cells B5:C8. Let’s get to the procedure below.
Steps:
- To write code in VBA, first, open the Developer tab and then select Visual Basic.
Then, it will open a new window of Microsoft Visual Basic for Applications.
- Now, open Insert >> select Module
.
- Type the following code in the VBA Module.
Sub RangeSelect()
Dim Rng1 As Range
Worksheets("selectRange").Activate
Set Rng1 = Range("B5:C8")
Rng1.Select
End Sub
Here, we set the range B5:C8 as Rng1. We select it by the Range Method of VBA. Keep that in mind that we need to activate our excel sheet so we activated the selectRange sheet.
- Now go back to the sheet and run Macro.
- After that, you will see the range B5:C8 selected automatically.
Thus you can set the desired range variable to selection using VBA.
Read More: Excel VBA: Get Range of Cells with Values (7 Examples)
2. Using VBA to Format Cells by Setting Range VariableÂ
Suppose our dataset looks like the following image.
We want to make the heading bold and AutoFit the columns. We can do this via VBA. Let’s see how we can do this.
Steps:
- First, open Visual Basic and type the following code in the VBA Module (to see how to open Visual Basic and VBA Module, please go to Section 1).
Sub SetRange()
Dim xyz As Range
Set xyz = Range("B4:C4")
xyz.Font.Bold = True
xyz.Select
Worksheets("autofit").Columns("B:C").AutoFit
End Sub
Here we set the range B4:C4 as xyz. Then we used the Bold method to make the fonts in cell B4 and C4 bold. We also fitted the columns B and C using the AutoFit method.
- Now, go back to the sheet and run the Macro which is named as SetRange.
After that, you will see the names clearly in the columns and the headers become bold and selected.
By following this path, you can format cells and AutoFit columns setting range variables in VBA.
Similar Readings
- How to Use VBA for Each Row in a Range in Excel
- How to Use VBA to Select Range from Active Cell in Excel (3 Methods)
- Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)
3. Copying a Range by Setting Variable Range Selection in VBA
Suppose we want to copy B6:C9 by setting the range variable to select. Let’s discuss the procedure below.
Steps:
- First, open Visual Basic and type the following code in the VBA Module (to see how to open Visual Basic and VBA Module, please go to Section 1).
Sub CopyRange()
  Dim cpy As Range
  Set cpy = Range("B6:C9")
  cpy.Copy
End Sub
Here, we simply copied the range B6:C9 by using the Copy method of VBA. We set the range B6:C9 as cpy.
- Now go back to your sheet and run Macros. Select CopyRange as it is the name of your current Macro.
You will see the range B6:C9 is copied.
You can paste this range anywhere in your Excel sheet by pressing CTRL + V. I pasted the range through B12 to C15.
By going along with this method, you can copy a range by setting the range variable to selection in Excel VBA.
Read More: Excel VBA: Copy Dynamic Range to Another Workbook
4. Format Cells with Color by Setting Range Variable to Selection
Suppose we want to color the 8th and 10th rows of the dataset with green. Let’s follow the description below.
Steps:
- First, open Visual Basic and type the following code in the VBA Module (to see how to open Visual Basic and VBA Module, please go to Section 1).
Sub ColorRange()
Dim color As Worksheet
Dim x1 As Range
Dim x2 As Range
Set color = ActiveSheet
Set x1 = Range("B8:C8")
Set x2 = Range("B10:C10")
x1.Cells.Interior.ColorIndex = 4
x2.Cells.Interior.ColorIndex = 4
End Sub
Here we define our range B8:C8 and B10:C10 as x1 and x2 respectively. We made our color Excel Sheet as ActiveSheet and we colored our desired ranges by ColorIndex property.
- Now go back to your sheet and run Macros. Select ColorRange as it is the name of the current Macro.
After that, you will see the desired ranges filled with green color.
Thus you can format the cells with color by setting the range variable to selection.
Read More: How to Use Range with Variable Row and Column with Excel VBA
Similar Readings
- VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)
- Excel VBA to Loop through Range until Empty Cell (4 Examples)
- How to Convert Range to Array in Excel VBA (3 Ways)
5. Deleting Rows by Setting Range Variable in VBA
Suppose we want to delete the 8th and 10th rows of the dataset with green. Let’s follow the description below.
Steps:
- First, open Visual Basic and type the following code in the VBA Module (to see how to open Visual Basic and VBA Module, please go to Section 1).
Sub ColorRange()
Dim color As Worksheet
Dim x1 As Range
Dim x2 As Range
Set color = ActiveSheet
Set x1 = Range("B8:C8")
Set x2 = Range("B10:C10")
x1.Cells.Interior.ColorIndex = 4
x2.Cells.Interior.ColorIndex = 4
End Sub
The ranges that we want to delete are B8:C8 and B10:C10. We named them as x1 and x2 respectively. Then we just deleted them by Delete method.
- Now go back to your sheet and run Macros. Choose DeleteRange as it is the name of your current Macro.
After that, you will see the ranges B8:C8 and B10:C10 are gone.
By following this approach, you can delete rows by setting the range variables to selection.
Practice Section
In the following image, you will find the dataset that we worked on in this article so that you can practice on your own.
Conclusion
In a nutshell, the article fully focuses on some applications to set range variable to selection by Excel VBA. We described some pretty basic methods. If you have any other ideas or feedback, kindly leave them in the comment box. This will help me enrich my upcoming articles
