VBA Range Cells

VBA Range Cells

In VBA, the range is also called as the property of an individual cell or a group of cells in any row or a column. The range is particularly a data set in excel. For example, cell A1 to cell B3 is a range of cells if they contain a value of some sorts or they are empty.

In VBA, we make codes to automate our tasks. To automate our tasks, we refer to value in excel cells. Those set of cells, either individual or multiple, are called range in excel. In VBA, we can specify which rows or column range we are referring to.

Syntax of Range in Excel VBA

VBA Range has the following syntax:

The syntax to use range expression in VBA is as follows:

Expression.Range(“Cell Range”)

To use range function, we need to understand three basic things in VBA –

• Object Qualifier: This is basically the workbook or the worksheet we are trying to refer to.
• Property: This is the function to use the properties of the object.
• Method: This function is what we want to do with the data we selected.

For example, Application.Workbooks(“ANAND.xlsm”).Worksheets(“Sheet1”).Range(“A1”) The object qualifier is Application.workbooks in which we referred to ANAND named excel file and Sheet 1 as worksheet if we have to change the font, then that would be our method. This will be clear with more examples.

Let us first change the font of our cell A2, which is the Value of “ANAND”, to bold using the VBA range function.

Follow the below steps to use VBA Range Function.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab go to the module to open the code window.

Step 3: Declare a sub-function in the code window. Cell A2 has in sheet 1.

Code:

Sub Sample()

End Sub

Step 4: Now, in the code, activate the worksheet by the following code.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now, we will change the font for the specific cell using the range function in VBA.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

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

End Sub

Step 6: Run the above code from the run button provided or press f5.

Once we have run the code, we can see the following result.

VBA Range Cells – Example #2

Now let us change the colors of the specific range of cells. For demonstration purpose, I have made cell range B2:C5 in red color. With the VBA range function, I want to select them and clear the font. Here is the original representation of those cells.

Follow the below steps to use Excel VBA Range Cells.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab to insert a new module to open the code window. Click on the module inserted to start writing the code.

Step 3: Declare a sub-function in the code window.

Code:

Sub Sample1()

End Sub

Step 4: Activate the worksheet with the below function.

Code:

Sub Sample1()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now, we select the cells and clear the format by using the range function.

Code:

Sub Sample1()

Worksheets("Sheet1").Activate

Range("B2:C5").ClearFormats

End Sub

Step 6: Run the code, and from the run, button provided see the result.

VBA Range Cells – Example #3

Let us merge few cells using the VBA range function. For demonstration purpose Let us see how the current cells look like without merge.

Follow the below steps to use Excel VBA Range Cells.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab to insert a new module to open the code window. Click on the module inserted to start writing the code.

Step 3: Declare a sub-function in the code window.

Code:

Sub Sample2()

End Sub

Step 4: Activate the workbook with the below function.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now, for the merge cells, we use the VBA Range function as follows.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

Range("A6:D6").Merge

End Sub

Step 6: Run the code by the run button or press f5 to see the result.

VBA Range Cells – Example #4

Now let us select a range of cells and change the color format for them. In example one, we had selected only one cell. But in this example, we will select a bunch of cells to blue color. For demonstration purpose, I have data in cell range E2:F4 as follows:

Follow the below steps to use VBA Range Cells.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab to insert a new module to open the code window. Click on the module inserted to start writing the code.

Step 3: Declare a sub-function in the code window.

Code:

Sub Sample3()

End Sub

Step 4: Activate the worksheet with the following code below,

Code:

Sub Sample3()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now, let us change the color of cell range E2:F4 using the VBA range function.

Code:

Sub Sample3()

Worksheets("Sheet1").Activate

Range("E2:F4").Interior.ColorIndex = 37

End Sub

Step 6: Run the code by pressing F5 or from the run button provided and see the result.

Things to Remember

• The range can be one cell or multiple cells.
• If we are referring to any object, we use the dot operator(.) such as range(“A1”).Select is used to select cell A1.
• To use range value in excel VBA, we need methods and properties of a cell.

Recommended Articles

This has been a guide to VBA Range Cells. Here we have discussed how to use Excel VBA Range Cells along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. VBA Replace
2. Excel Named Range
3. VBA Number Format
4. VBA IsError