Cells and Range

In the previous chapters you have learnt about Workbooks and Worksheets and in this chapter, you will learn about the Cells, Columns, Entire Columns, Rows, Entire Rows, Range, Selection, Used Range, Current Region, and Special Cells.

Table of Contents

Cell

Syntax of the cells is Cells([RowIndex], [ColumnIndex]). For example, if we must select a cell “B3” then we must put cells (3, 2). Row number 3 and column number 2.

Sub Select_a_Cell()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Cells(3, 2).Select

End Sub

We can also use cells(3 , “B”).

Sub Select_a_Cell()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Cells(3, “B”).Select

End Sub

Columns

We can refer the any column by using Column’s property of worksheet.

For example, we have to the select the column B then

Sub Select_a_column()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Columns(2).Select

End Sub

We can also select the Column B by using below code

Sub Select_a_column()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Columns("B:B").Select

End Sub

Entire Column

Column B can be selected by below code:

Sub Select_a_column()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Cells(1, 2).EntireColumn.Select

End Sub

Rows

We can refer the any row by using Rows property of worksheet.

For example, we have to the select the row number 2 then

Sub Select_a_row()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Rows(2).Select

End Sub

We can also select the Row number 2 by using below code

Sub Select_a_row()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Rows("2:2").Select

End Sub

Entire Row

Row number 2 can be selected by below code:

Sub Select_a_row ()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Cells(2, 1).EntireRow.Select

End Sub

Range

Range is used to refer a cell or group of cells.

For Example, if we want to select the Range “A1:D10” then below is the code.

Sub Select_a_range()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("A1:D10").Select

End Sub

To select the Range “A1:D10” we can use below given code also

Sub Select_a_range()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range(sh.Cells(1, 1), sh.Cells(10, 4)).Select

End Sub

Selection

Selection is used to refer the current selected range in the excel sheet. We can use selection for multiple purpose.

Below is the example for change the cell background color of selected range.

Sub Selection_range()

    Selection.Interior.Color = vbRed

End Sub

Used Range

Used range refers the entire data range which has been used in our excel worksheet. This is extremely useful to use the dynamic range of worksheet.

Below is the example for change the cell background color of used range of worksheet.

Sub Used_range()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.UsedRange.Interior.Color = vbRed

End Sub

Current Region

Current Region refers the region of the given range. To check the current region of any range or cell select that cell and press Ctrl+A. Whatever range will be selected that is current region.

Below is the example to change the background color of current region of cell “A1”

Sub Current_Region()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("A1").CurrentRegion.Interior.Color = vbRed

End Sub

Special Cells

“Special cells” is used to refer those cells in given range which only have numbers, text, blank cells, formulas, cells with data validation, cells with conditional formatting, the last cell in the worksheet, cells with comments or all visible cells.

For example, if we want to select the cells in our worksheet which have formulas

Sub Special_Cells()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets(“Sheet1”)

sh.UsedRange.SpecialCells(xlCellTypeFormulas).Select

End Sub