How to quickly select a large non contiguous range with keyboard in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > How to quickly select a large non contiguous range with keyboard in excel

← Previous post -

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?

Quickly select a range of values in excel

Count unique distinct values in a large dataset with a date criterion

Excel udf: Remove duplicates from a large dataset

Filter unique distinct and duplicate values from a large data set in excel 2007

Quickly search a table using many criteria

Recommended categories

Excel

Comments (4)

4 Responses to “How to quickly select a large non contiguous range with keyboard in excel”

Jamieson says:
April 28, 2010 at 10:13 pm
How about:
1. CTRL+END
2. CTRL+SHIFT+HOME
Is this what you mean to do?
Reply
Oscar says:
April 29, 2010 at 8:44 am
Jamieson,
Yes! Much better!
Thanks for commenting!
Reply
Johannes says:
November 18, 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.
Reply
Oscar says:
November 20, 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.
Reply