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.

Download Practice Workbook

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

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

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: [Fixed!] Rows and Columns Are Both Numbers in Excel

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.

Read More: Excel VBA: Set Range by Row and Column Number (3 Examples)

Similar Readings

• How to Switch Rows and Columns in Excel Chart (2 Methods)
• Excel Macro: Convert Multiple Rows to Columns (3 Examples)
• How to Transpose Column to Multiple Rows in Excel (6 Methods)
• Hide Rows and Columns in Excel: Shortcut & Other Techniques
• How to Add Multiple Rows and Columns in Excel (Every Possible Way)

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: Find Value in Row and Return Column Number Using VBA in Excel

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 VBA Split function to split the cell address by the delimiter “$” to separate the row and column number. 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.

Read More: [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)

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.

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 Convert Multiple Rows to Columns in Excel (9 Ways)
• Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)
• How to Transpose Multiple Columns to Rows in Excel
• Switch Rows and Columns in Excel (5 Methods)
• Freeze Rows and Columns at the Same Time in Excel
• Excel VBA to Set Range Using Row and Column Numbers (4 Examples)