Excel VBA: Get Row and Column Number from Cell Address (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.

  • 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 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

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

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


Method 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

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 Row and Column Number 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

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


Method 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-

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

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


Related Articles


<< Go Back to Rows and Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo