Return Row Number of Active Cell in Excel

As you are probably aware by now, options with Visual Basic for Applications (VBA) are virtually limitless. One of the things that VBA can certainly help with is locating your cells and a certain text in a file.

In the example below, we will show how to return the row number of any cell, and how to return the row number of an active cell.

How to Return Row Number

There is a rather simple formula in Excel that can retrieve a row number of any desired cell. We will have a list of random numbers, like in the picture below:

If we want to find the row number for number 42 (it is row number 9, but regardless), all we need to input in cell B2 is the following formula:

=ROW(A9)

This is the result we will end up with:

Graphical user interface, application, table, Excel

Description automatically generated

This formula can be useful for many things, by itself or in combination with other formulas.

How to Return Row Number of Active Cell

To retrieve a row number of a specific cell, an active cell, in particular, we will need to use VBA. To open it, we will click the combination of ALT + F11 on our keyboard. On the window that appears, we will right-click on the left side, and choose Insert >> Module:

Graphical user interface, application

Description automatically generated

Once the new window opens, we will insert a simple code on the right side:

Sub ActiveRow()
  MsgBox "We are currently in a row number " & ActiveCell.Row
End Sub

This code gives us the message box that shows the row of the cell we are in.

Let’s suppose we are in cell D6. When we execute the code by pressing F5 in our module, this is the message we receive:

Graphical user interface

Description automatically generated

Which is exactly the information we want to retrieve.