VBA Cells in Excel (Select, Get, Count, Format, Copy-Paste, etc.)

Cells property in Excel VBA (Visual Basic for Applications) is used for interacting with and modifying cells in Microsoft Excel. In simpler terms, it can read or modify the content of cells, allowing for more advanced and automated spreadsheet operations.

In this Excel tutorial, we’ll show you, how we can use the VBA Cells property with different objects and functions to perform various operations like select cells, get value, format cells, copy and paste cells, count cells, split comments, and notes, etc.

⏷VBA Cells Property
⏷Syntax of Cells Property
⏷Difference Between Ranges and Cells
⏷Select Cells in Excel Using VBA
 ⏵Selecting A Single Cell
 ⏵Selecting All Cells with Data
 ⏵Selecting Last Cell in a Column
⏷Get Cell Value Using VBA in Excel
 ⏵Reading Cell Value
 ⏵Getting Cell Value Into Variable
⏷Format Cells in Excel Using VBA
 ⏵Changing Number Format
 ⏵Changing Fill Color
 ⏵Changing Font Style, Size, and Color
⏷Copy and Paste Cells in Excel Using VBA
⏷Count Cells in Excel Using VBA
 ⏵Counting Cells in a Range
 ⏵Counting Cells Based on Criteria
⏷Set Cell Value
⏷Clear Cells
⏷Define Range
⏷Extract Comments from Cells
⏷Extract Notes From Cells
⏷Change Cell Reference With the Offset Function
⏷Refer to Non-Contiguous Cells
⏷Find Cells with Duplicate Values

What Is VBA Cells Property?

Cells property refers to a cell for any operation in VBA. It can access or modify cells based on row and column indexing.

Cells property can be used as a part of the Range object, the Worksheet object, or by itself.

• Range.Cells

Range.Cells refer to a cell in any specified range. To specify a single cell in that range, we can use the Cells property. Cells property with the Range object is commonly used to access individual cells with a loop for a specific operation.

• Worksheet.Cells

Worksheet.Cells refer to a cell in any specified worksheet. By specifying the row and column number, we can access any cell of that worksheet. This property is useful for performing a cell-by-cell dynamic operation on an entire sheet.

What Is the Syntax of Cells Property in Excel VBA?

The generic syntax of the Cells property is:

With any application object, the syntax is:

Here, expression is replaced with Range, Worksheet, Sheets, etc. objects.

Example of Cells function :

This code will define the cell “A1” in the active worksheet. By changing the RowIndex and ColumnIndex, we can define different cells.

Example of Cells property with Worksheet object :

This code will define the “A1” cell of the worksheet named “Sheet1”.

Example of Cells property with Range object:

This code will define the “B4” cell in the B4:C10 cell range.

What Is the Difference Between Ranges and Cells Properties in Excel VBA?

The difference between Range and Cells is that Range can refer to one or more cells, while Cells can only refer to only one cell at a time. The hierarchical relation between these two objects is:

Workbook → Worksheet → Range → Cells

The Cells are used for root-level change in a particular cell while the Range covers multiple cells for any operation. Both Cells and Range can refer to a single cell like the examples below.

To refer to the cell “A1”, the Cells function code will be: Cells(1, 1)

Using Range, the code will be: Range(“A1”)

However, Range can be used to define an entire range of cells and non-contiguous cells which is not possible using only the Cells function.

For example, the code Range(“A1:B4”) will refer to all cells in the A1 to B4 range. For referring to non-contiguous cells, the range function can be used like this: Range(“A1”, ”C1”, ”E1”).  This will refer to the non-adjacent cells A1, C1, and E1.

How to Select Cells in Excel Using VBA

We can use the Cells.Select method to select cells in Excel VBA. Using this property, we can select specific cells or cells based on conditions. Here, we will demonstrate 3 examples of using Cells.Select method to select cells.

To demonstrate these examples, we will use the following dataset that contains the Order No, Retail Price, Order Quantity, and Total of a random shop.

Selecting A Single Cell

In this example, we will select a single cell using the Cells function in VBA. Follow the steps below.

• First, go to the Developer tab → you will see the Visual Basic option under the Code group of command.

• Click the Visual Basic option to open the Microsoft Visual Basic for Applications window.
• After that, click the Insert menu and you will see the Module option.

• Select the Module option and you will get Module1. Enter the following code in this module.

Sub Selecting_a_Single_Cell()
Cells(6, 2).Select
End Sub

• Now, click the Run button (or press F5 on the keyboard) and you will see that cell B6 is selected in the worksheet like in the image below.

Selecting All Cells with Data

If the worksheet has data scattered all over it, selecting them individually might be difficult. However, we can use a VBA code to quickly select all cells with data. Apply the following steps:

• Enter the following code in a module.

Sub Selecting_Cells_With_Data()
Set data_rng = ThisWorkbook.ActiveSheet.UsedRange
data_rng.Cells(1, 1).Select
For rw = 1 To data_rng.Rows.Count
    For col = 1 To data_rng.Columns.Count
        If data_rng.Cells(rw, col).Value <> "" Then
            Union(Selection, data_rng.Cells(rw, col)).Select
        End If
    Next col
Next rw
End Sub

• After clicking the Run button, the above code will select all the cells with data shown in the gif below.

Selecting Last Cell in a Column

Sometimes you may need to go to the last cell in a column. For large datasets, doing this manually may take some time. We can use the Cells property in VBA to quickly select the last cell in a column.

You can apply the steps below to select the last cell in a column using VBA.

• Enter the following VBA code in a Module.

Sub Selecting_Last_Cell_in_a_Column()
Dim reference_cell As Range
' Taking a cell as input to identify the column
Set reference_cell = Application.InputBox("Select any cell of the desired column:", Type:=8)
If reference_cell.Cells.Count > 1 Then
MsgBox "Please select one cell"
Exit Sub
End If
Dim lastRow As Integer
lastRow = Cells(Rows.Count, reference_cell.Column).End(xlUp).Row
Cells(lastRow, reference_cell.Column).Select
End Sub

• After pressing F5 or running the code, an input box will show up asking you to select a cell of the column you want to find the last cell with data.

Here, I have selected cell D10.

• After selecting the cell and clicking on OK, the last cell of the column containing data will be selected like in the image below.

How to Get Cell Value Using VBA in Excel

Getting the cell value implies using the value to show or use it for other operations. We can use the Cells properties directly to show the cell value. In this section, we will learn both reading cell value and getting cell value into a variable.

Reading Cell Value

We will show the value of a cell in a message box. We will take a cell as an input and read its value to show it in a message box.

• Insert the following VBA code in a module.

Sub Reading_Cell_Value()
Dim ref_cell As Range
Set ref_cell = Application.InputBox("Select the cell:", Type:=8)
If ref_cell.Cells.Count > 1 Then
MsgBox "Please select only one cell"
Exit Sub
End If
MsgBox ref_cell
End Sub

• Running this code will trigger the input box asking for a cell input to read. It will show an error message if you select more than one cell. Here, I have selected the C9 cell.

• After giving the desired input cell, you will see the cell value in a message box like it is in the image below.

Getting Cell Value Into Variable

We will get data into a string variable from the Retail Price column. Then we will show the variable value in a message box.

• Insert the following code into a module.

Sub Getting_Cell_Value_Into_Variable()
Dim ValStr As String
Dim ref_cell As Range
Set ref_cell = Application.InputBox("Select the cell:", Type:=8)
If ref_cell.Cells.Count > 1 Then
MsgBox "Please select only one cell"
Exit Sub
End If
ValStr = ref_cell.Text
MsgBox ValStr
End Sub

• After writing the code, press the run button or press F5 on the keyboard. This will trigger the input box. Select the desired cell for getting the cell value into the variable. Here I selected the C9 cell.

• After selecting the cell and pressing OK, you will see a message box showing the value like in the image below.

How to Format Cells in Excel Using VBA

Using different formatting properties with the Cells function, we can format cells in Excel with VBA. We can change the Number Format, Fill Color, Font Properties, etc. With the examples below, we will demonstrate these formatting with the VBA Cells function.

Changing Number Format

In our dataset, the Retail Price is in General format. We will change that to a custom format with one decimal place and a dollar sign ($) at the front. We will use the Cells.NumberFormat property in this case.

• Insert the following code in a module.

Sub Changing_Number_Format()
Dim rng As Range
Set rng = Application.InputBox("Select the range of cells to change number format:", Type:=8)
If rng.Cells.Count = 0 Then
MsgBox "No values found in this range"
Exit Sub
End If
rng.Cells.NumberFormat = "$###0.0"
End Sub

• Running the code will trigger an input box asking for the range to change. Select the range you want to change the number format. Here I have selected the cell range C6:C15.

• After giving input and clicking on OK, you will see the change in number format in the Retail Price column like in the image below.

Changing Fill Color

We can change the Fill Color of any cell using the Cells.Interior.Color property.

• Insert the following code in a module.

Sub Changing_Fill_Color()
' Changing color using RGB value
Cells(6, 2).Interior.Color = RGB(255, 200, 100)
' Changing color using color constants
Cells(7, 2).Interior.Color = vbYellow
' Changing color using color code
Cells(8, 2).Interior.Color = 255
' Changing color using color index
Cells(9, 2).Interior.ColorIndex = 37
End Sub

• Run this code. This will format the cells with different colors based on the RGB value, color constants, color code, and color index.

Changing Font Style, Size, and Color

In this section, we will change the font style, font size, and font color. We can use the Cells.Font properties here.

• Insert the code below into a Module.

Sub Changing_Font_Style_Size_Color()
Dim rng As Range
Set rng = Application.InputBox("Select the range of cells you want to change the font:", Type:=8)
If IsEmpty(rng) Then
MsgBox "Please insert some values first"
Exit Sub
End If
With rng.Cells.Font
.Name = "Times New Roman"
.Size = 12
.Color = vbRed
.Bold = True
.Italic = True
End With
End Sub

• Run the code. Upon running the code, the input box will pop up asking to select the range to be formatted. Here I selected the D6:D15 cell range.

• After selecting the range and pressing OK, you will see the changes in font properties like in the image below.

How to Copy and Paste Cells in Excel Using VBA

We want to copy a range of cells and paste it. We will use the Cells.Copy Destination:= property.

• Insert the following code into a module.

Sub Copy_and_Paste()
Dim rng As Range
Dim des As Range
Set rng = Application.InputBox("Select the range you want to copy:", Type:=8)
Set des = Application.InputBox("Select the first cell where you want to paste the values:", Type:=8)
If des.Cells.Value <> 0 Then
MsgBox "The destination cell is not empty"
Exit Sub
End If
rng.Cells.Copy Destination:=des
End Sub

• After running, it will first ask to select a range to copy. Then it’ll ask to select the destination cell. The output will be like the following gif.

How to Count Cells in Excel Using VBA

To count the cells in a range, we can use the Cells.Count property. We will show you 2 examples below to count cells in a range and count cells in a range based on certain conditions with Excel VBA.

Counting Cells in a Range

We want to count the number of cells in a given range and then show it in a message box.

• Insert the following code into a module.

Sub Counting_Cells_in_Range()
Dim rng As Range
Dim num As Integer
Set rng = Application.InputBox("Select the range:", Type:=8)
num = rng.Cells.Count
MsgBox "The number cells in the range is " & num
End Sub

• Run the code. There will be a message box asking to select the range of cells to count. Here I have selected the range C8:D13.

• After selecting the range and pressing OK, you will see the number of cells in a message box like in the image below.

Counting Cells Based on Criteria

We will be counting the number of cells in the Order Quantity column that have a value of less than 20.

• Insert the following code into a module.

Sub Counting_Cells_with_Criteria()
Dim rng As Range
Dim num As Integer
Dim min As Integer
num = 0
Set rng = Application.InputBox("Please select the Range:", Type:=8)
If rng.Cells.Count <= 1 Then
MsgBox "Please select more than one cell"
Exit Sub
End If
min = Application.InputBox("Put the minimum criteria:", Type:=1)
rng.Cells(1, 1).Select
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
If rng.Cells(i, j).Value >= min Then
num = num + 1
End If
Next j
Next i
MsgBox "Count of Cells Having More Than the Value " & min & " is: " & num
End Sub

• Run the VBA code. After selecting the cell range and giving the minimum value for the criteria, the output will be shown in a message box like in the gif below.

How to Set Cell Value with Excel VBA

We want to set up the C6 cell value to 5. We can directly set a cell value in Excel VBA by using the Cells property to address the cell and set the value.

• Insert the following code in a module.

Sub Setting_Cell_Value()
Cells(6, 3).Value = 5
End Sub

• After running the code, we will see the value of the cell C6 has been changed to 5 as in the image below.

How to Clear Cells Using Excel VBA

We will clear the Total column values. So we will use the Cells.Clear property. This clears the cell data along with their formatting.

• Insert the following code in a module.

Sub Clearing_Cells()
Dim rng As Range
Set rng = Application.InputBox("Please select the range of cells you want to clear:", Type:=8)
rng.Cells.Clear
End Sub

• Running the code will trigger the input box asking you to select the range of cells that you want to clear. Here I have selected the E6:E13 cell range.

• After selecting the range and clicking on OK, you will see the range of cells has been cleared along with formatting like in the image below.

How to Define Range with Excel VBA

Usually, we use the actual reference of a range to define a range. However, we can use the Cells property to define a range as well. We require the row and column index of the cell in the first row & first column, and the cell in the last row & last column.

For example, we will change the Fill Color of the cell range B6:B15 to Yellow. To define this range with the Cells property and change the Fill Color, you can apply the following steps.

• Insert the following code in a module.

Sub Defining_Range()
Range(Cells(6, 2), Cells(15, 2)).Interior.Color = vbYellow
End Sub

• Running this code will change the fill color of the specified range to Yellow like in the image below.

How to Extract Comments from Cells

We will extract comments from the cells in the Total column. We can use the Cells.CommentThreaded property.

The Total column contains comments like in the image below.

• Insert the VBA code in a module.

Sub Split_Comment()
Dim input_range As Range
Set input_range = Application.InputBox("Extract comment from which range of cells:", Type:=8)
For i = 1 To input_range.Rows.Count
If Not input_range.Cells(i, 1).CommentThreaded Is Nothing Then
input_range.Cells(i, 2).Value = input_range.Cells(i, 1).CommentThreaded.Text
input_range.Cells(i, 1).CommentThreaded.Delete
End If
Next i
End Sub

• After running the code, an input box will appear asking you to select a specified range for splitting comments. Here I have selected the cell range E6:E15.

• After selecting the range and clicking on OK, we will see the comments in a separate column like in the image below.

How to Extract Notes from Cells with VBA

We can split the notes from a range of cells just like splitting comments if they contain notes like in the image below.

Use the code below to split notes from a specific cell range.

• Insert the following code in a module.

Sub Split_Notes()
Dim input_range As Range
Set input_range = Application.InputBox("Extract note from which range of cells:", Type:=8)
For i = 1 To input_range.Rows.Count
If Not input_range.Cells(i, 1).Comment Is Nothing Then
input_range.Cells(i, 2).Value = input_range.Cells(i, 1).Comment.Text
input_range.Cells(i, 1).Comment.Delete
End If
Next i
End Sub

• After running the code, you will see an input box asking for the specified range. Here I have selected the cell range E6:E15.

• After selecting the specified range and clicking on OK, we will see the notes in a new column like it is in the below image.

How to Change Cell Reference With the Offset Function Using VBA

We can use the Offset function with the Cells object to change the cell reference to another cell. For example, Cells(1,1).Offset(2,3) refers to Cell(3,4). Here, the Offset function has moved specified cells row index 2 rows down and column index 2 columns down.

In this section, we will change the references with the help of the VBA Offset function and then highlight different cells. Use the VBA code below.

• Insert the following code in a module.

Sub Changing_Cell_Ref_With_Offset()
Cells(6, 2).Offset(0, 1).Interior.ColorIndex = 15
Cells(6, 3).Offset(1, 0).Interior.ColorIndex = 37
Cells(6, 3).Offset(1, 1).Interior.ColorIndex = 7
End Sub

• After running the code, you will notice the change of cell references with different colors like in the image below.

How to Refer Non-Contiguous Cells with Excel VBA

We will highlight some non-contagious cells using the Cells property with the Union function.

• Insert the following code in a module.

Sub Refer_to_Non_Contigious_Cells()
Union(Cells(6, 2), Cells(9, 2), Cells(15, 2)).Interior.Color = vbYellow
End Sub

• Run the VBA code and you will see the non-contagious cells are highlighted like in the image below.

How to Find Cells with Duplicate Values Using VBA

We will use the following code to highlight duplicate values in a selected range. For this, we will use Interior.Color with Cells object.

• Insert the following VBA code module:

Sub Finding_Duplicate_Cells()
Dim rng As Range
Dim i As Integer
Dim j As Integer
Set rng = Application.InputBox("Select the range:", Type:=8)
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
If WorksheetFunction.CountIf(rng, rng.Cells(i, j).Value) > 1 Then
rng.Cells(i, j).Interior.Color = vbYellow
End If
Next j
Next i
End Sub

• Running this code will trigger an input box asking you to select the range where you want to find the duplicates. Here I have selected the cell range C6:C15.

• After selecting the range and clicking on OK, we will see the duplicates are highlighted as in the image below.

Download Practice Workbook

This article has shown how to use the VBA Cells property for different purposes and the difference between Range and Cells. Moreover, we showed how we can use the Cells properties to perform real-life applications like selecting cells, getting value, formatting cells, copying and pasting cells, counting cells, splitting comments and notes, etc. Feel free to leave a comment for any further queries.