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

How to Write Code in the 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.

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.

Method 1 – Get the Row and Column Numbers 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

Excel VBA Get Row and Column Number from Cell Address

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

Excel VBA Get Row and Column Number from Cell Address

Method 2 – Get the Row and Column Numbers from the 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

Excel VBA Get Row and Column Number from Cell Address

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.

Excel VBA Get Row and Column Number from Cell Address

Method 3 – Find a Specific String to Get the Row and Column Numbers from Its Cell Address

Task: 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

Excel VBA Get Row and Column Number from Cell Address

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.

Excel VBA Get Row and Column Number from Cell Address

Method 4 – Use of VBA Split Function to Get the Row and Column Numbers from a 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-

Excel VBA Get Row and Column Number from Cell Address

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