Daily Dose of Excel » Blog Archive » Select Adjacent - The Diigo Meta page

• 04 Dec 07
  

  Jeremy Gollehon

  Cool selection code to select adjacent selections of cells.

  excel vba

  • 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

Would you like to comment?

Join Diigo for a free account, or sign in if you are already a member.