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.

**Table of Contents**hide

**Download Practice Workbook**

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

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

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:** **Excel VBA: Insert Formula with Relative Reference (All Possible Ways)**

**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 Use FormulaR1C1 Absolute Reference in Excel VBA**

**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**.

**Read More: How to Use Cell References in Excel Formula (All Possible Ways)**

**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.

**Read More:** **Excel VBA: Cell Reference in Another Sheet (4 Methods)**

**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**.

**Similar Readings**

**How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)****Absolute Cell Reference Shortcut in Excel (4 Useful Examples)****Mixed Cell Reference in Excel (4 Examples)****How to Find and Replace Cell Reference in Excel Formula****[Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)**

**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.

**Read More:** **How to Reference Cell by Row and Column Number in Excel (4 Methods)**

**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.

**Read More:** **Cell Reference in Excel VBA (8 Examples)**

**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.

**Read More: Different Types of Cell References in Excel (With Examples)**

**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. Don’t forget to have a look at our website **Exceldemy.com** for more articles on various Excel-related topics, and please give your valuable suggestions in the comment box.