One of my clients asked me if there was a way to quickly select non-blank cells in an Excel worksheet – similar to the way you would use GoTo->Special->Blank Cells. As I was explaining the VBA technique that would allow him to do this, I could see by his 1000-yard stare that he was allergic to VBA programming. So I gave him my quick and easy shortcut key trick.
The Shortcut key is Crtl+Shift+\. This will essentially select any cell in a range that is not the same value as the active cell.
The idea is to select a target range of columns, starting each column with a blank cell. The starting blank cells become the active cell for each respective column. Now when you press Crtl+Shift+\, any cell that does not equal the active cell (in this case, blank) will be selected.
Ctrl+Shift+\ is really just a boosted version of Goto->Special->Column Differences. It selects all the differences in a particular column.
Remember: When using this trick, each column within your target range must start with a blank cell. Take a look at this next example.
234 will not be selected because the starting value in the second column of the selected range is 234.
Bonus Tip
You can also use Ctrl+Shift+\ to quickly jump through the values in a table. This will allow you to quickly find the value you need without playing the scroll-bar dance.
Roy Maclean recently posted a neat trick that leverages this technique to explode a data table into multiple workbooks. It’s definately worth a look.
RELATED STUFF
This may be the answer to a conundrum I’ve been working with.
Thanks Mike!
Thanks from me, too! This will be a time saver once I burn it into my brain (meanwhile it lives on a sticky stuck to the side of the monitor)
Here is another way to select the non-blank cells via the keyboard (it doesn’t matter if the first cell in the columns is blank or not); just execute the following keystrokes one after another (if you look at the screen when keying them in, you should get the idea why it works)…
Ctrl-f
* (an asterisk, which is keyed in using Shift-8)
Alt-i
Ctrl-a
Alt-F4
@Mike/Rick
Both these methods will not catch a cell that has a single quote and is there fore not blank
Ctrl+Shift+\ will not work for formulas that return a zero length string
1. F5- Special – Constants( All ticked) OK
2. Color Cells – Yellow
3. F5- Sepcial – Formulas (All ticked) OK
4. Color Cells – Yellow
5. Ctrl+F – Find Format – Yellow
6. Alt+i
7. Ctrl+a.
8 Alt+F4