Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select column cells to the left/right of active selection - an example
No question here, just a procedure for the archive.
This procedure was originally developed to "extend" the select capablities PUPv6Range 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 PUPv6Range 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to select entire row and column in a sigle cell selection | Excel Worksheet Functions | |||
Select Active Cells | Excel Worksheet Functions | |||
how to select fewer cells without canceling original selection | Excel Discussion (Misc queries) | |||
I do not see the selection colour when I select some cells | Excel Discussion (Misc queries) | |||
Tools/Options/Charts-Active cells is dimmed. Want to select leave | Charts and Charting in Excel |