Selecting Next Cells In A Different Order

If you have ever used Excel, you probably know already that you can move from your current cell to the one to the right, or below by using the tab or return keys.  So what if you want  to select the next cell, but the next cell is not in the normal order?

For example, a form in Excel, you want the user to go from one field to the next which may will not be directly to the right or below the current cell, how would you do it?

The image below is an extreme version of what I’m talking about.  Starting from B2, you want to go to E2 next, then B4, then B5, then B6, then B3, then C13…. and so on.

The trick to doing this is:

1. Select your second field (E2)
2. Hold down the Ctrl key and click on the remaining cells in order
3. Then, with Ctrl key pressed, click on the first cell
4. When all are selected, you should have something like the image below
5. In my case, the Name Box says “B2”.  Overwrite this and give the range your own name, but make it mean something and no spaces are allowed.  I will name my selection “unusual_order”.

Now you have a named range.

To demonstrate that this has worked, click on any one cell.

Select your named range by clicking on the arrow at the Name Box.

This will select all cells in your named range, and the first cell selected will be the cell we want to start from (which was the last cell we selected in the steps above).

Press either the tab or return keys and you will see the active cells cycle through your named range in the order that you specified.

VBA

For the above, you have to select the named range and then work from there.  You could add VBA code so that the named range is automatically select when opening the sheet or file.  The code you need is Range(“unusual_order”).select – replace unusual_order with your named range.