Excel VBA with Cell Reference by Row and Column Number – 11 Examples

This is the sample dataset.

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

Step 1:

• Go to the Developer tab.
• Choose Record Macro.
• Set a name for the Macro and click OK.

Step 2:

• Click Macro.
• Select the Macro and choose Step Into.

Step 3:

• Go to the command module.
• Enter the following code.

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

Step 4:

• Press F5 to run the code.

The salary is displayed in D5.

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

Example 2 – Insert Values in Multiple Cells Using the Row and Column Number with Excel VBA

Step 1:

• Press Alt+F11.
• Copy and paste the following code in 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:

• Press F5 to run the code.

The salary is displayed in the column.

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

Step 1:

• Press Alt+F11.
• Copy and paste the following code in the module.

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

Step 2:

• Press F5 to run the code.

This is the output.

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

Example 4 – Use the Cells Method to Reference a Cell

Step 1:

• Press Alt+F11.
• Copy and paste the following VBA code in 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 F5.

This is the output.

Example 5 – Read Data with VBA Using Cell Reference by Row and Column Number

Step 1:

• Press Alt+F11.
• Copy and paste the following code in the module.

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

Step 2:

• Press F5 to run the code.

This is the output.

Example 6 – Get the Last Row Number Using the Column in Cell Reference

Step 1:

• Press Alt+F11.
• Copy and paste the following code in 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:

• Press F5 to run the code.

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

Step 1:

• Press Alt+F11.
• Copy and paste the following code in 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:

• Press F5 to run the code.

This is the output.

Example 8 – Select a Range in the Dataset with Excel VBA

Step 1:

• Press Alt+F11.
• Copy and paste the following code in the module.

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

Step 2:

• Press F5 to run the code.

The data range is selected.

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

Step 1:

• Press Alt+F11.
• Copy and paste the following code in 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:

• Press F5 to run the code.

This is the output.

Example 10 – Find the Last Cell in the Dataset using Cell Reference in Excel VBA

Step 1:

• Press Alt+F11.
• Copy and paste the following code in 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:

• Press F5 to run the code.

The cursor moved to the last cell of the dataset.

Example 11 – Create a VBA Function Based on Cell Reference by Row and Column Number

Step 1:

• Press Alt+F11.
• Copy and paste the following code in the module.

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

Step 2:

• Enter the following formula any cell of the dataset.

Step 3:

• Press Enter.

This is the output.

Download Practice Workbook

Download the practice workbook to exercise.

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