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:
This is the result we will end up with:
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:
Once the new window opens, we will insert a simple code on the right side:
MsgBox "We are currently in a row number " & ActiveCell.Row
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:
Which is exactly the information we want to retrieve.