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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select column cells to the left/right of active selection - an example
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 -- Regards, Tom Ogilvy |
Reply |
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 |