The cell is the smallest part of an Excel worksheet. And the range is a collection of the cells. We can represent a cell in different ways. Usually, we represent cells in “A1” style in Excel, where A represents the row and 1, the column. A1 indicates the cell in the 1st column and in the 1st row. This article will show 11 examples of Excel VBA codes to use cell reference by row and column number.

**VBA Codes with Cell Reference by Row and Column Number in Excel: 11 Examples**

We can use the row and column number in multiple ways to refer to a cell with Excel. To demonstrate the examples, we are going to use the following dataset where we will insert salary in it.

**Example-1: Insert Value in a Single Cell Using Reference by Row and Column Number with Excel VBA**

In the first example, we are going to insert a value in a single cell using the row and column number in the cell reference.

**Step 1:**

- Go to the
**Developer**tab. - Choose the
**Record Macro**option. - Set a name for the
**Macro**and then press**OK**.

**Step 2:**

- Click on
**Macro**from the ribbon. - Select the
**Macro**and**Step Into**it.

**Step 3:**

- Go to the command module.
- Put the following code on the command module.

```
Sub Row_Column_Number_1()
Worksheets("Dataset1").Cells(5, 4).Value = 1000
End Sub
```

**Step 4:**

- Hit the
**F5**button to run the code.

So, we’ve inserted the salary at **Cell D5** perfectly.

**Read More:** How to Reference Cell by Row and Column Number in Excel

**Example-2: Insert Values in Multiple Cells Using Row and Column Number with Excel VBA**

In the second example, we are going to input values in multiple cells using the row and column number with **Excel VBA**. Follow the steps below for this.

**Step 1:**

- Press
**Alt+F11**to enter the command module. - Copy and paste the following code on the module.

```
Sub Row_Column_Number_2()
Dim n As Integer
Dim m As Integer
m = 1000
For n = 5 To 9
If n > 5 Then
Cells(n, 4).Value = Cells(n - 1, 4).Value + 100
Else
Cells(n, 4).Value = 1000
End If
Next n
End Sub
```

**Step 2:**

- Hit the
**F5**button to run the code.

We input the salary in multiple cells with one click. No need to input values to each cell separately.

**Read More:** How to Use Variable Row Number as Cell Reference in Excel

**Example-3: Use A1 Notation to Reference a Cell by Row and Column Number in Excel VBA**

In the third example, we will use **A1 Notation** to reference a cell in an **Excel VBA** code.

**Step 1:**

- Hit
**Alt+F11**keys and enter the command module. - Copy and paste the below code on the command module.

```
Sub Row_Column_Number_3()
Range("D5").Value = 1000
End Sub
```

**Step 2:**

- Hit
**F5**and run the code.

Here it is! We’ve directly inserted the cell reference in this method.

**Read More:** How to Reference a Cell from a Different Worksheet in Excel

**Example-4: Use Cells Method to Reference a Cell**

In the fourth example, we will use the **Cells** method with the range as a cell reference in **Excel VBA**. Row and column numbers are used in the **Cells** command.

**Step 1:**

- Enter the command module by pressing
**Alt+F11**. - Copy and paste the following
**VBA**code on the command module.

```
Sub Row_Column_Number_4()
Dim n1, n2 As Range
Set n1 = Range(Cells(5, 4), Cells(7, 4))
Set n2 = Range(Cells(8, 4), Cells(9, 4))
n1.Value = "IT"
n2.Value = "Accounts"
End Sub
```

**Step 2:**

- Run the code by pressing the
**F5**button.

Here, we insert the **Cells **command with the range so that we can use the row and column number in **Excel VBA**.

**Example-5: Read Data with VBA Using Cell Reference by Row and Column Number**

In the fifth example, we want to read data from the dataset using cell reference by row and column number. Please follow the steps below.

**Step 1:**

- Press the
**Alt+F11**button to enter the command module. - Copy and paste the following code on the common module.

```
Sub Row_Column_Number_5()
Dim name As String
name = Cells(5, 2)
MsgBox "Employee name is: " & name
End Sub
```

**Step 2:**

- Now run the code and press
**F5**for this.

Finally, we get the employee name from the dataset.

**Example-6: Get the Last Row Number Using the Column in Cell Reference**

In the sixth example, we want to find out the last row number of a specific column. Follow the below steps for this.

**Step 1:**

- Enter the command module by pressing the
**Alt+F11**keys. - Copy and paste the
**VBA**code below on the module.

```
Sub Row_Column_Number_6()
Dim final_row As Long
final_row = Cells(Rows.Count, "B").End(xlUp).Row
MsgBox "Last Row: " & final_row
End Sub
```

**Step 2:**

- Now, hit the
**F5**key to run the code.

Here, **Row 9** is the last row that contains data in **Column B**.

**Example-7: Find the Last Column Using the Row Number in Cell Reference**

In the seventh example, we will find out the last column number with the help of the row number on the **Excel VBA** code.

**Step 1:**

- Enter the
**VBA**command module by pressing**Alt+F11**. - Copy and paste the following code on the module.

```
Sub Row_Column_Number_7()
Dim final_column As Long
final_column = Cells(5, Columns.Count).End(xlToLeft).Column
MsgBox "Last Column is: " & final_column
End Sub
```

**Step 2:**

- Run the code by pressing the
**F5**button.

We find out the column number with the help of the row number.

**Example-8: Select a Certain Range in the Dataset with Excel VBA**

In the eighth example, we will select a certain range in the dataset using cell reference by row and column number in **Excel VBA**.

**Step 1:**

- Hit
**Alt+F11**and enter the command module. - Put the following
**VBA**code.

```
Sub Row_Column_Number_8()
Range(Cells(5, 2), Cells(9, 4)).Select
End Sub
```

**Step 2:**

- Run the code by pressing the
**F5**button.

Our data range is selected from the dataset.

**Example-9: VBA Macros to Color the Selection and Select a Cell Using Row and Column Number in Cell Reference**

In the ninth example, we will change the color of the selection range. After that, we will select a cell from that range.

**Step 1:**

- Enter the command module by pressing
**Alt+F11**. - Copy and paste the following code on the module

```
Sub Row_Column_Number_9()
Range(Cells(5, 2), Cells(9, 4)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
End With
Range(Cells(5, 2), Cells(9, 4)).Cells(3, 2).Select
End Sub
```

**Step 2:**

- Run the code by pressing
**F5**.

First, we change our selected range. Then, we go to a certain cell of the selected range.

**Example-10: Find the Last Cell in the Dataset by Cell Reference in Excel VBA**

In the second last example, we will find the last cell of our dataset.

**Step 1:**

- Open the command module by pressing
**Alt+F11**. - Copy and paste the following
**VBA**code on the module.

```
Sub Row_Column_Number_10()
Dim final_row As Long
Dim final_column As Long
final_row = Range("B4").End(xlDown).row
final_column = Range("B4").End(xlToRight).column
Cells(final_row, final_column).Select
End Sub
```

**Step 2:**

- Run the code by pressing
**F5**.

After running the code, the cursor moved on the last cell of the dataset.

**Example-11: Create a VBA Function Based on Cell Reference by Row and Column Number**

Lastly, we will create a **VBA **function based on the row and column numbers.

**Step 1:**

- Go to the command module by pressing
**Alt+F11**. - Paste the following code and save the code.

```
Function GetCellValue(row As Integer, col As Integer)
GetCellValue = ActiveSheet.Cells(row, col)
End Function
```

**Step 2:**

- Now, go to any cell of the dataset. Put the following formula:

`=Cell_Value(5,3)`

**Step 3:**

- Now, press the
**Enter**button.

Corresponding data is shown here.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we have shown 11 examples of Excel VBA to reference cells by row and column number. I hope this will satisfy your needs. Ask us if you have any queries.

**Related Articles**

- How to Display Text from Another Cell in Excel
- How to Reference Text in Another Cell in Excel
- How to Find and Replace Cell Reference in Excel Formula
- How to Use Cell Value as Worksheet Name in Formula Reference in Excel
- How to Use OFFSET for Cell Reference in Excel
- Excel VBA: Cell Reference in Another Sheet
- How to Reference Cell in Another Sheet Dynamically in Excel

**<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel**