Jeremy Gollehon's Bookmarks tagged VBA →  View Popular

Using the original sub as a starting point, I fumbled my way thru VBA to create a modification that allows the selection can be anywhere to the right or left of the column to be operated upon. Simply put, it calculates the required offset, then selects the range. Seems to work OK, but does anyone see any pitfalls? I have no need for a keyboard shortcut, but I do have the need to do formula fill-downs. However, if some of the cells in the target range are not populated, then it stops. In that event I have another sub (ActivateWhatever --- not sure where I got it from) that deals with this. Of course sometimes it goes far beyond the range of interest,

Sub SelectTargetCol()

Dim rAdjacent As Range

Dim SetOff As Integer

Set TRange = Application.InputBox(Prompt:="WhichColumn?", Type:=8)

SetOff = ActiveCell.Column - TRange.Column

If TypeName(Selection) = "Range" Then

If Selection.Cells.Count = 1 Then

If Not IsEmpty(Selection.Offset(0, -SetOff).Value) Then

With Selection.Offset(0, -SetOff)

Set rAdjacent = .Parent.Range(.Cells(1), .End(xlDown))

End With

Selection.Resize(rAdjacent.Cells.Count).Select

End If

End If

End If

End Sub

Sub ActivateWherever()

'this identifies the last used row anywhere on the sheet & allows you to specify where selection starts

Dim Last As Double, Where As Double

Where = InputBox(Prompt:="Which row?")

Last = Cells.Find(What:="*", After:=[a1], _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

Range((Cells(Where, ActiveCell.Column)), Cells(Last, ActiveCell.Column)).Select

End Sub