How to quickly select a non contiguous range
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell range.
The picture above shows a part of a list that has 3000 values 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.
Here is how to select the entire list:
- Select cell B2
- Press CTRL + SHIFT + END
You have now selected the entire non-contiguous list.
VBA Macro
If you record a macro while pressing CTRL + SHIFT + END you get the following code:
Sub Macro1() Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select End Sub
Make sure you select the first value in the column before you run the macro.
Get Excel *.xlsm file
Select a non contiguous range.xlsm
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
How to replace part of formula in all cells
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Highlight cells based on coordinates
The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B […]
Count unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Filter unique distinct values, sorted and blanks removed from a range
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
Label groups of duplicate records
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]
5 Responses to “How to quickly select a non contiguous range”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
How about:
1. CTRL+END
2. CTRL+SHIFT+HOME
Is this what you mean to do?
Jamieson,
Yes! Much better!
Thanks for commenting!
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.
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.
Just two steps - Ctrl Space Bar, Ctrl shift up