up vote 7 down vote favorite
3
share [g+] share [fb]

I know I can select all cells in a particular column by clicking on column header descriptor (ie. A or AB). But is it possible to then exclude a few cells out of it, like my data table headings?

Example

I would like to select data cells of a particular column to set Data Validation (that would eventually display a drop down of list values defined in a named range). But I don't want my data header cells to be included in this selection (so they won't have these drop downs displayed nor will they be validated). What if I later decide to change validation settings of these cells?

How can I selection my column then?

A sidenote

I know I can set data validation on the whole column and then select only those cells that I want to exclude and clear their data validation. What I would like to know is is ti possible to do the correct selection in the first step to avoid this second one.

I tried clicking on the column descriptor to select the whole column and then CTRL-click those cells I don't want to include in my selection, but it didn't work as expected.

link|improve this question
84% accept rate
feedback

4 Answers

up vote 5 down vote accepted

Click on the first cell you want to be selected and then press CTRL + SHIFT + ARROW DOWN.

link|improve this answer
This is fine when I have an empty data part of my worksheet. But if I have some filled-in rows this only selects cells up to the last cell that has any data within the same row. – Robert Koritnik Dec 28 '09 at 11:35
What if you press it again? – Mario Marinato -br- Dec 28 '09 at 12:27
+1 for simply awesome. I was just searching for something like this, and it works beautifully. – Breakthrough Jul 6 '11 at 13:21
feedback
up vote 1 down vote

You can type F5 to bring up the name box. Then type B3:B65536 and click OK. 65536 for Excel 2003 and earlier, 1048576 for 2007 and 2010.

link|improve this answer
feedback
up vote 0 down vote

random guess On some other products, you can use (for instance) "B3:" to mean "All cells in column B from row 3 down". I'm not sure if Excel works this way.

link|improve this answer
I'm not looking for a programmatic solution, but rather a clickable manual solution. – Robert Koritnik Dec 28 '09 at 11:33
1  
FYI: Excel works fine if you write B3:B – Robert Koritnik Oct 13 '10 at 16:31
1  
B3:B doesn't work for me from VBA – L. De Leo Dec 6 '10 at 21:54
feedback
up vote -1 down vote

Right-click and choose Hide on the rows you don't want to get selected and then select whole columns.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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