How to Hyperlink to the First Empty Cell in an Excel Column Range
It’s easy to hyperlink to a cell, or even a named cell/range. It’s even easy to get the first empty cell in column range (a range of cells in a column). But hyperlinking to that cell is a bit of a process. The following command though makes it super easy!
Summary of Behavior
- If the column range is empty, goes to the first cell in the range
- If the column range is full, goes to the last cell in the range
- Otherwise, goes to the first empty cell in the column range
Here is how you do it for an example column range B2:B9:
=HYPERLINK("#" & CELL("address", IF(SUMPRODUCT(MAX((ROW(B2:B9)*(B2:B9<>"")))-1)<0, INDEX(B2:B9,0), INDEX(B2:B9, MIN(ROWS(B2:B9), SUMPRODUCT(MAX((ROW(B2:B9)*(B2:B9<>"")))-1))))), "What to show as the hyperlink text")
We subtract 1 because ranges are 1-indexed rather than 0-indexed. However, if the range is empty, SUMPRODUCT will return -1 because of the subtraction, so we use an IF to make sure our range has at least one value, otherwise we just return the first cell in the range, which is empty. Similarly, if there are no empty cells, we want to just return the location of the last cell in the range rather than breaking, so we use MIN(ROWS, …) to help guarantee that. The “&” concatenates the hyperlink symbol “#” with the address of the first empty cell. We get that address using “INDEX” and convert it to readable format using “CELL”. (Doing it this way also avoids having to mess with sheet names, etc.)
The generic formula is:
=HYPERLINK("#" & CELL("address", IF(SUMPRODUCT(MAX((ROW(START_CELL:END_CELL)*(START_CELL:END_CELL<>"")))-1)<0, INDEX(START_CELL:END_CELL,0), INDEX(START_CELL:END_CELL, MIN(ROWS(START_CELL:END_CELL), SUMPRODUCT(MAX((ROW(START_CELL:END_CELL)*(START_CELL:END_CELL<>"")))-1))))), "hyperlink_text")
There you go! Now you’ll be able to jump to the first empty cell in any given column range without difficulty, and the hyperlink will adjust automatically even if you insert rows and columns into your workbook!
