6

In most versions of Excel (I'm using 2010), pressing ctrl+up or ctrl+down will take you to the edge of the current data region or the start of the next data region in a worksheet. Effectively skipping "empty cells", i.e. cells where =isblank() returns TRUE.

I frequently set up a column in a worksheet with the formula eg:

=IF(MID($C2,5,1)=" ", "space", "")

to highlight rows where something I'm looking for is true, in this example: when the 5th character in the cell in column C is a space, the cell in the new column will be "space" and if it is not, the cell in the new column will be blank.

Visually, this is a good cue for finding the data rows you're interested in, and you can use a Filter to display only those rows that match.

I would like to be able to use ctrl+up or ctrl+down or some similar keyboard shortcut to skip between the cells with content and skip over the blank cells, but this doesn't work as the blank cells still contain a formula and are not "empty" cells. Is there any way, I could achieve this behaviour?

Short version:

Is there any keyboard shortcut in Excel to skip over cells which show no value, but aren't "empty cells"?

Or, is there any value I can return from a function (e.g =NA()) that will trick Excel into thinking the cell is empty so the default ctrl+arrow keys shortcut will skip them?

1
  • I guess I could do it with VBA code in a worksheet_change() or worksheet_calculate() event but I'm not sure it's worth that much effort - and I'd need to rewrite the code every time.
    – Luke
    May 29, 2013 at 4:02

6 Answers 6

5

No, but set it up this way, and Tab and Shift+Tab will take you to the next and previous highlighted row. First do this:

  1. Change your formula to =IF(MID($C2,5,1)=" ", 1, "") (copying down, of course)
  2. Highlight the column (Ctrl+Space will do that if you're in any cell in that column).
  3. Press F5, then Alt+Special, Formula, de-select Text, then Enter

    • To exclude errors, de-select Errors in step 3 also. (e.g., when LEN($C2)<5, but you could also change the formula to account for that)

Now the Tab key will step you through each cell in that column that has a numeric value in it (1). It also has the added bonus that in step 2 above, you can see the total of the highlighted rows in the status bar at the bottom of the screen (Sum:).

2
  • 1
    Excellent! If I don't need dynamic results I can even de-select numbers in step 3 and hit delete, and then ctrl+up/down works perfectly!
    – Luke
    Dec 3, 2014 at 23:56
  • It's complicated but a nice idea +10 ☺ Feb 17, 2020 at 6:06
4

In short: No.

The Ctrl+arrow keys will recognise only truly empty cells. There is no keyboard shortcut out of the box that can evaluate cell contents to jump to the end of a cell range with similar content, like formula results, error values or similar.

1
  • It's one of the built in KB shortcuts.. quite useful ! +1 ☺ Feb 17, 2020 at 6:10
3

You could do Ctrl + F to Find that True or False data value you want. Click on Find All to get a List displayed in the dialog box which shows you all the locations where your formula is true of false.

Do you need more than just jumping around to the different areas?

2

Combining the other two answers, you could create a macro to fit your custom needs. The macro could be bound to whatever keys you like. You could create a series of macros for search up, down, left, and right and bind them to different keys.

Make the macro hold the current cell contents and then move one step in your chosen direction and compare the contents. If it is same, keep moving, if it is different, move the selection.

You will need some Visual Basic knowledge to make this relatively simple macro, and I don't know the syntax off hand, but it is possible.

2

You could replace all your blank cells with something like zzzzz. Be sure to choose the option Match Entire cell contents if your data has blanks. Then select your range to copy, paste it were you need it. Then change all the zzzzz to a blank on the new source.

1
  • I don't get exactly what you're saying, but changing to blank is a good idea! If you changed the formula to =IF(MID($C2,5,1)=" ", "space", "zzzzz") then used Copy, Paste Values on the column, then did Replace zzzzz with nothing (blank), then Ctrl+Down and Ctrl+Up would work to skip the blanks! (I know there's conditions for when you can Replace in Values instead of Formulas, but Copy, Paste Values was easier than researching that.) Oct 25, 2014 at 5:27
-1

Instead of

=IF(MID($C2,5,1)=" ", "space", "")

Use

=IF(MID($C2,5,1)=" ", "space", "0")

Then FIND AND REPLACE all of the 0s with an empty replace field.

1
  • “I can’t open this bottle of pancake syrup; the cap is stuck.”  /  “Well, just break the bottle with a hammer; then you’ll be able to get to the syrup.”  Your answer is destructive (it changes the worksheet) and it doesn’t work! Feb 16, 2020 at 22:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .