Selection of Cells in VBA

1. Select a Single Cell in VBA
2. Select a Range of Cells in VBA
3. Select a Range of Non-Contiguous Cells in VBA
4. Select All Cells in a Worksheet in VBA
5. Select a Row in VBA
6. Select a Column in VBA
7. Select the Last Non-Empty Cell in a Column in VBA
8. Select the Last Non-Empty Cell in a Row in VBA
9. Select the Current Region in VBA
10. Select the Cell Relative to Another Cell in VBA
11. Select a Named Range in VBA
12. Select the Cell on Another Worksheet in VBA
13. Manipulate the Selection Object in VBA

In this following article, we discuss the selection of cells in VBA and how we can select the range of cells in VBA.

VBA allows us to select a cell, ranges of cells, or all the cells in the worksheet. You can manipulate the selected cell or content using the Selection object.

Select a Single Cell in VBA

We can choose a cell in a worksheet using the Select method. The following code will select cell B2 in the ActiveWorksheet.

# VBA
Range("B2").Select

Or we can use another method as shown below.

# vba
Cells(2, 2).Select

The output of both codes is shown below.

Select a Range of Cells in VBA

We can select a group of cells in a worksheet using the Select technique and the Range object. The following code will select A1:B5 as shown below.

# VBA
Range("A1:B5").Select

Output:

Select a Range of Non-Contiguous Cells in VBA

In VBA, you can choose cells or ranges that are not next to each other by dividing the cells or ranges using a comma. The following code will grant you to select cells B1, D1, and F1, as shown below.

# VBA
Range("B1, D1, F1").Select

Output:

We can also select sets of non-contiguous ranges using VBA. The following code will select A1:A5 and B5:B8, as shown below.

# VBA
Range("A1:A5, B5:B8").Select

Output:

Select All Cells in a Worksheet in VBA

We can select all the cells in a worksheet with the help of VBA. The code below will select all the cells in a worksheet.

# VBA
Cells.Select

Output:

Select a Row in VBA

We can select a specific row in a worksheet with the help of the Row object and the index number of the row we want to select. The code below will select the first row in our worksheet.

# VBA
Rows(2).Select

Output:

Select a Column in VBA

We can select a specific column in a worksheet with the help of the Column object and the index number of the column we want to select. The following code will select column B in our worksheet below.

# VBA
Columns(2).Select

Output:

Select the Last Non-Empty Cell in a Column in VBA

We have data in cells B1, B2, B3, and B4, and we want to select the last non-blank cell, which might be cell B4 in the column. We can use VBA to do this and the Range.End method.

The Range.End method can take these four options: xlToLeft, xlToRight, xlUp and xlDown.

The code below will select the last non-blank cell, B4 in this case, if B1 is the active cell, as shown below.

# VBA
Range("B1").End(xlDown).Select

Output:

Select the Last Non-Empty Cell in a Row in VBA

Let’s suppose we have data in cells A2, B2, C2, D2, and E2, and we want to select the last non-blank cell, which might be cell E2 in the row. We can use VBA and the Range.End method.

The code below will select the last non-blank cell, E2 in this case, if A2 is the active cell, as shown below.

# VBA
Range("A2").End(xlToRight).Select

Output:

Select the Current Region in VBA

We can use the CurrentRegion property of the Range object to select a rectangular range of blank and non-blank cells around a given input cell. If we have data in cells A2, B2, and C2, the following code will select this region around cell A2:

# VBA
Range("A2").CurrentRegion.Select

Output:

Select the Cell Relative to Another Cell in VBA

We can use the Offset property to select a cell relative to another cell. The next code shows us how to select cell C3, two rows, and two columns close to cell A1.

# VBA
Range("A1").Offset(2, 2).Select

Output:

Select a Named Range in VBA

We can select named ranges as well. Let’s say we have named cells B1:B4 Code.

We can use the following code to select this named range, as shown below.

# VBA
Range("Code").Select

Output:

Select the Cell on Another Worksheet in VBA

We first need to activate the sheet using the Worksheets.Activate method to select a cell on another worksheet. The next code will allow you to select cell A4 on Sheet2.

# VBA
Worksheets("Sheet2").Activate
Range("A4").Select

Output:

Manipulate the Selection Object in VBA

Once we have selected a cell or range of cells, we can refer to the Selection object to manipulate these cells. The following code chose the cells B1:D1 and set the font of these cells to Calibri, the font weight to Bold, the font style to Italic, and the fill color to Blue.

# VBA
Sub newFunc()

Range("B1:D4").Select
Selection.Font.Name = "Calibri"
Selection.Font.Bold = True
Selection.Font.Italic = True
Selection.Interior.Color = vbBlue

End Sub

Output:

Use the With Construct in VBA

We can relate the above example using the With or End With statement to refer only once to the Selection object. This keeps typing and normally makes your code easier to read.

# VBA
Sub newFunc()

Range("B1:D4").Select
With Selection
.Font.Name = "Calibri"
.Font.Bold = True
.Font.Italic = False
.Interior.Color = vbBlue

End With
End Sub

Output: