Excel 2010 Macro Select or Deselect Noncontiguous Range

Normally, to deselect a single cell or range on a sheet, click an unselected cell to deselect all cells and then start over by reselecting all the correct cells. This may become inconvenient if you need to reselect a lot of noncontiguous cells. This sample adds two new options to the contextual menu of a selection: Deselect ActiveCell and Deselect ActiveArea. With the noncontiguous cells selected, hold down the Ctrl key, click the cell you want to deselect to make it active, release the Ctrl key, and then right-click the cell you want to deselect. This will prompt the contextual menu. Click the menu item that deselects either the one active cell or the contiguously selected area of which it is a part of.

Dim x As Range, y As Range

If Selection.Cells.Count > 1 Then

For Each y In Selection.Cells

If y.Address <> ActiveCell.Address Then

If x Is Nothing Then

Set x = y

Else

Set x = Application.Union(x, y)

End If

Next y

If x.Cells.Count > 0 Then

x.Select

End If

End Sub

Sub DeselectActiveArea()

Dim x As Range, y As Range

If Selection.Areas.Count > 1 Then

For Each y In Selection.Areas

If Application.Intersect(ActiveCell, y) Is Nothing Then

If x Is Nothing Then

Set x = y

Else

Set x = Application.Union(x, y)

End If

Next y

x.Select

End If

End Sub

Add the following procedures to the ThisWorkbook module:

Private Sub Workbook_Activate()

ModifyRightClick

End Sub

Private Sub Workbook_Deactivate()

Application.CommandBars(“Cell”).Reset

End Sub