Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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 '13 at 4:02

5 Answers 5

up vote 1 down vote accepted

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:).

share|improve this answer
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 at 23:56

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.

share|improve this answer

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?

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
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.) –  GlennFromIowa Oct 25 at 5:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.