Excel VBA: Get Row and Column Number from Cell Address (4 Methods)

This article illustrates how to get the row and column number from cell address using VBA code in Excel. We’re going to use several built-in functions to accomplish the task conveniently.

How to Get Row and Column Number from Cell Address Using Excel VBA: 4 Methods

Write Code in Visual Basic Editor

To get row and column numbers from cell addresses, we need to open and write VBA code in the Visual Basic Editor. Follow the steps to open the Visual Basic Editor and write some code there.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

1. Get Row and Column Number from a Specific Cell Address Using VBA in Excel

Task: Get the row and column number of cell B4 using VBA in Excel.

Solution: we’ll use the Range.Row property and Range.Column property in our VBA code to get the row and column number of the specified cell address. These two properties return the number of the first row and the first column of a specific range.

Code: Insert the following code in the Visual Basic Editor and press F5 to run it.

Sub GetRowColNumberfromCellAddress()
    rowNumber = Range("B4").row
    colNumber = Range("B4").Column
    MsgBox "Row Number: " & rowNumber & vbCrLf & "and" & vbCrLf & "Column Number: " & colNumber
End Sub

Output: We’ve successfully output the row and column numbers i.e., 4 and 2 of cell address B4 in the Msg Box.

Read More: How to Delete Empty Rows and Columns in Excel VBA

2. Get Row and Column Number from Active Cell Address

Task: Get the row and column number of the active cell in the worksheet using VBA in Excel.

Solution: We need to use the Application.Selection property in Excel VBA to return the currently selected object (cell address, in this case) of the active worksheet.

Code: Insert the following code in the Visual Basic editor and press F5 to run it.

Sub GetRowColNumberfromCellAddress()
    rowNumber = Selection.row
    colNumber = Selection.Column
    MsgBox "Row Number: " & rowNumber & vbCrLf & "and" & vbCrLf & _
    "Column Number: " & colNumber
End Sub

Output: We’ve successfully output the row and column numbers i.e., 1 and 1 of the selected cell address A1 in the Msg Box.

3. Find a Specific String to Get Row and Column Number from Its Cell Address 

Task: From the following dataset, we want to find the student name Stuart and then get the row and column number of the containing cell using VBA in Excel.

Solution: We can use the VBA Find function to search for a specific value within a range and return the first instance of it.

Code: Insert the following code in the Visual Basic editor and press F5 to run it.

Sub GetRowColNumberfromCellAddress()
    Dim findName  As Range
Set findName = ActiveSheet.Cells.Find("Stuart")
If Not findName Is Nothing Then
    MsgBox "Row Number: " & findName.row & vbCrLf & _
    "and" & vbCrLf & "Column Number: " & findName.Column
Else
    MsgBox "Student not found!"
End If
End Sub

Output: The above code successfully found the student name “Stuart” in cell C8 and output the row and column numbers i.e., 8 and 3 in the Msg Box.

Read More: Excel VBA to Set Range Using Row and Column Numbers

4. Use of VBA Split Function to Get Row and Column Number from Cell address in Excel

Task: Get the row and column number of the active cell in the worksheet using the Split function in Excel VBA.

Problem Analysis: Run the following code in the Visual Basic editor to get the cell address of the active cell in the worksheet.

Sub GetRowColNumberfromCellAddress()
    MsgBox Selection.Address
End Sub

The output is here-

We see that the cell address shown in the Msg Box is $B$2, in an absolute cell reference form.

Solution:  We can use the VBA SPLIT function to split the cell address by the delimiter “$” to separate the row and column numbers. While the syntax of the SPLIT function is-

Split(expression, [delimiter], [limit], [compare])

Code: Insert the following code in the Visual Basic editor and press F5 to run it.

Sub GetRowColNumberfromCellAddress()
    rowNumber = Split(Selection.Address, "$")(1)
    colNumber = Split(Selection.Address, "$")(2)
    MsgBox "Row Number: " & rowNumber & vbCrLf & _
    "and" & vbCrLf & "Column Number: " & colNumber
End Sub

Output: We’ve successfully output the row and column numbers i.e., B and 2 of the active cell address B2 in the Msg Box.

Things to Remember

The SPLIT function stores the result as an array. That’s why we output the first element of the array as the row number.  And the 2nd element in the array is the column number in the last example.

Download Practice Workbook

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

Conclusion

Now, we know how to get row and column numbers from cell addresses using VBA in Excel. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Add Rows and Columns in Excel
• How to Find Difference Between Rows and Columns in Excel
• How to Switch Rows and Columns in Excel
• [Fixed!] Missing Row Numbers and Column Letters in Excel
• How to Switch Rows and Columns in Excel Chart
• How to Lock Column Width and Row Height in Excel
• [Fixed!] Rows and Columns Are Both Numbers in Excel