Select column cells to the left/right of active selection - an example

[aztecbrainsurgeon]

No question here, just a procedure for the archive.

This procedure was originally developed to "extend" the select
capablities PUPv6>Range Tools>
Select By Value. PUPv6 is an Excel add-in developed by JWalk &
Associates: http://j-walk.com/ss/pup/pup6/index.htm
In PUPv6 Range Tools you can only select the entire row based on a
single column
(Selection Type: Select Rows Based on a Single Column)
This procedure allows you to select a partial row (e.g. B2:F2 instead
of A2:IV2)
by:
1. using PUPv6>Range Tools> Select By Value> Selection Type: Select
Cells (instead of Select Rows
2. evaluating a single column using "Select Cells In This Range" (e.g.
$D:$D)
3. Running this procedure after the Select By Value tool has returned a
selection.

Note: You don't need the PUPv6 add-in to get the benefits of this
procedure.
This code can work after any other manual or automated cell selection
method has been used.



Sub RangeSelectionOffset()

'Selects column cells to the left or the right of the
'active selection(s). This VBA Procedure can work on non-contiguous
ranges.
'You are prompted for the column number offset either left (-)
'or right (+).

Dim OriginalAddress, AddressOffset1, UnionRange As Range
Dim i As Long
Dim ColumnOffsetNumber As Integer

On Error Resume Next
Set OriginalAddress = Selection

ColumnOffsetNumber = Application.InputBox(prompt:="Enter # of
columns to offset select. Remember a positive (+) value SELECTS TO THE
RIGHT, a negative (-) value SELECTS TO THE LEFT.", _
Title:="Select Rows To
The Left(-) or The Right(+)", Default:=-1, Type:=1) 'type 1 is
number


'Test for a positive (+) column offset number

If ColumnOffsetNumber > 0 Then

For i = 0 To ColumnOffsetNumber

Set AddressOffset1 = OriginalAddress.Offset(0, i)

If UnionRange Is Nothing Then
Set UnionRange = Application.Union(AddressOffset1,
OriginalAddress)
Else
Set UnionRange = Application.Union(AddressOffset1,
UnionRange)
End If

Next i

UnionRange.Select

End If
'Test for a negative (-) column offset number

If ColumnOffsetNumber < 0 Then

For i = 0 To -ColumnOffsetNumber

Set AddressOffset1 = OriginalAddress.Offset(0, -i)

If UnionRange Is Nothing Then
Set UnionRange = Application.Union(AddressOffset1,
OriginalAddress)
Else
Set UnionRange = Application.Union(AddressOffset1,
UnionRange)
End If

Next i

UnionRange.Select

End If


End Sub

Search criteria:
column cells offset select partial rows select cells to the left or
right select column offset
enhance selection add cell range or ranges expand selection based on
current selection adding areas
isolate partial rows part of a row expand scattered selection choose
more cells partial row

 

[Tom Ogilvy]

Here is another useful tip:

An alternative would be to hold down the shift key and click in the cell to
which you want to extend the selection.

This would minimize the need of having to go to Tools=>Macro=>Macros and
running this macro or using a shortcut key combination, figuring out the
number of columns left or right you want to go and making sure you included
the negative sign if going to the left, entering that information in an
input box and clicking the OK button. If bad mouse skills cause too much to
be selected, clicking in the original cell and trying again will work. If
you select short of the desired mark, continue to hold the shift key and try
again.

If code is desired, this is more compact:

Sub RangeSelectionOffset()

'Selects column cells to the left or the right of the
'active selection(s). This VBA Procedure can work
'on non-contiguous ranges.
'You are prompted for the column number offset either left (-)
'or right (+).

Dim AddressOffset1 As Range
Dim ColumnOffsetNumber As Integer

On Error Resume Next
Set AddressOffset1 = ActiveCell(1)
ColumnOffsetNumber = Application.InputBox( _
prompt:="Enter # of columns to offset " & vbNewLine & _
"select. Remember a positive (+) value " & vbNewLine & _
"SELECTS TO THE RIGHT, a negative (-) " & vbNewLine & _
"value SELECTS TO THE LEFT.", _
Title:="Select Rows To The Left(-) or The Right(+)", _
Default:=-1, Type:=1) 'type 1 is Number


'Test for a negative (-) column offset number

If ColumnOffsetNumber < 0 Then
Set AddressOffset1 = AddressOffset1 _
.Offset(0, ColumnOffsetNumber)
End If
AddressOffset1.Resize(1, _
Abs(ColumnOffsetNumber) + 1).Select
End Sub