How to quickly select a large non contiguous range with keyboard in excel
Filed in Excel on Apr.28, 2010. Email This article to a Friend
Here is a picture of a non contigous list in excel. The list has 3000 rows with occasional blanks. How do we quickly select the entire list?
The first thing that comes to mind is selecting this list using CTRL + SHIFT + DOWN ARROW but as you might know the selection stops at every blank cell. We need to find a quicker way to select this list.
Let us assume you have cell A1 selected.
I suggest you try:
- Press RIGHT ARROW
This will take you to the first blank column. In this example column B. (Cell B1) - Press CTRL + DOWN ARROW
This will take you to the last row on column B. Cell B1048576 (Excel 2007) - Press LEFT ARROW
This will take you back to column A. Cell A1048576 (Excel 2007) - Press CTRL + UP ARROW
This will take you to the last not blank row in list - Press CTRL+SHIFT+HOME
- Optional: Adjust selection. SHIFT + ARROW KEYS
You have now selected the entire non contiguous list.
Do you know a quicker way to select a large non contiguous list?
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
April 28th, 2010 at 10:13 pm
How about:
1. CTRL+END
2. CTRL+SHIFT+HOME
Is this what you mean to do?
April 29th, 2010 at 8:44 am
Jamieson,
Yes! Much better!
Thanks for commenting!
November 18th, 2010 at 1:20 pm
For anybody else who is wondering: this actually doesn't select a non contiguous range like it says in the header.
Within the article the statement changes to "non contiguous list" which is correct (though i'm not sure if this term even exists in excel lingo).
To explain using the example table from the article:
Non contiguous range: A1:A5,A7:A12,A14:A18
Non contiguous list: A1:A18
After doing what is described in the article A1:A18 is selected and not the non contiguous range.
November 20th, 2010 at 11:12 am
Johannes,
As far as I know, a cell range can be anything from:
One single cell
A column or row
Multiple columns and rows
If I remember it correctly, my intention was to describe how to select cell range containing blank cells or blank rows/columns quickly.