Select the First Cell in the Range in Excel

In this tutorial, we will look at the following 3 methods that we can use to select the first cell address in a given range.

You can use this address and retrieve the value of the cell using the INDIRECT function.

Method 1: ADDRESS with ROW, COLUMN, and MIN and Go To…

We will use the following data range named Country_GDP (A2:C6) to show how this method can be used to select the first cell in the range:

The first cell in this data range can be selected by using the following steps:

1. Select Cell F2 and type in the formula:

2. Press the Enter key and the reference for the first cell in the named range will be displayed:

3. Open the Go To dialog box by going  to Home >> Editing >> Find & Select >> Go To… on the Excel Ribbon:

Alternatively, we can press the keyboard shortcut Ctrl + G to launch the Go To dialog box.

4. In the Go To dialog box that pops up, type in the cell reference that was displayed in step 2 above and press OK:

The first cell in the named range is selected:

The Go To… feature enables jumping to the first cell in the data range.

Explanation of the Formula

This formula uses the combination of  ADDRESS, MIN, ROW, and COLUMN functions.

The ADDRESS function creates a cell reference as text, given specified row and column numbers.

The MIN function returns the smallest number in a set of values.

The ROW function returns the row number of a reference.

The COLUMN function returns the column number of a reference.

In this instance, we want to get the first row and the first column used by the named data range Country_GDP (A2:C6).

To get the first row used we use the ROW function and the MIN function as follows:

Because the data range Country_GDP has more than one row the ROW function returns an array of row numbers:

{2;3;4;5;6}

This array is passed to the MIN function which returns 2 as the smallest value:

To get the first column we use the COLUMN function and the MIN function as follows:

Because the data range Country_GDP has more than one column the COLUMN function returns an array of column numbers:

{1;2;3}

This array is passed to the MIN function which returns 1 as the smallest value:

The values returned by the ROW and COLUMN functions are then passed to the ADDRESS function which creates a reference to the cell at row 2 and column 1:

It returns an absolute reference of $A$2. If we want to get a relative reference, we need to input value 4 as the third argument as follows:

This will return a relative cell reference of A2.

Method 2: Use CELL together with the INDEX and Go To…

We will use the following data range named Country to show how this method can be used to select the first cell in the range:

The first cell in the named range can be selected using the following steps:

1. Select Cell D2 and type in the formula:

2. Press the Enter key to display the reference of the first cell in the range:

3. Press the keyboard shortcut Ctrl + G to launch the Go To dialog box. Alternatively, you can go to Home >> Editing >> Find & Select >> Go To … on the Excel Ribbon.
4. In the Go To dialog box that pops up type in the cell reference that was displayed in step 2 above and press OK:

The first cell in the range is selected:

The Go To feature enables jumping to the first cell in the data range.

Explanation of the Formula

This formula uses the combination of the CELL and INDEX functions.

The CELL function returns information about the formatting, location, or contents of the first cell, according to the sheet’s reading order, in a reference.

The INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

In this instance, we want to get the first row and the first column used by the named data range Country (A2:B6).

We give the INDEX function the arguments 1 for row number, 1 for column number, and Country for array:

It returns a reference to A2.

We then use the CELL function together with ”address’‘, to show the address of the first cell in the range.

Method 3: Use the Excel VBA

We will use the following data range named Country_GDP2 to show how this method can be used to select the first cell in a range:

To apply the Excel VBA to select the first cell in a named range, we use the steps below:

1. Press Alt + F11 to open the Visual Basic Editor (VBE). Alternatively, go to Developer >> Code >> Visual Basic on the Excel Ribbon:

2. In the Project Window of the Visual Basic Editor right-click the workbook and insert a new module:

3. In the new module type in the following code:

4. Save the module and the workbook as a macro-enabled workbook.
5. Press Alt + F11 to switch back to the active worksheet. Alternatively, click the View Microsoft Excel button:

6. Press Alt + F8 to launch the Macro dialog box. Alternatively, go to Developer >> Code >> Macros on the Excel Ribbon:

7. In the Macro dialog box select the SelectFirstCell Macro and click Run:

The first cell in the data range Country_GDP2 is selected:

Explanation of the code

Using the Dim statement, the myRange variable is declared as a range object.

The Set statement, is used to assign the range object Country_GDP2 to the myRange variable.

The select method of the Range object is used to select the first cell in the named range.

Conclusion

In this tutorial, we have explored 3 methods that we can use to select the first cell in a data range in Excel.

We can use the ADDRESS function together with the ROW, COLUMN, MIN functions, and the Go To… feature. We can also use the CELL function together with the INDEX function and Go To feature. Lastly, we can use the Excel VBA.

You can use the method that you are most comfortable with and best fits your work situation.