Author: Oscar Cronquist Article last updated on December 03, 2018

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:

  1. Select cell B2
  2. 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