Excel VBA Examples with Cell Reference by Row and Column Number

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.

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
• How to Use Relative Reference in Excel Macro (with Easy Steps)

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:

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.

Related Articles

• Difference Between Absolute and Relative Reference in Excel
• Absolute Reference in Excel (With Examples)
• Excel Sheet Name in Formula Dynamic (3 Approaches)
• Reference Another Sheet in Excel (3 Methods)
• How to Lock a Cell in Excel Formula (2 Ways)
• Example of Mixed Cell Reference in Excel (3 Types)
• [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)