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)