Selecting cells next to a scattered range

Question

I'm having difficulty selecting a range of cells in VBA. I already have a range of cells that are scattered, but am trying to add the three cells to the right of each cell in the range to the selection.

So far I've tried: SelectedRange("Hi").Resize(1, 4).Select, which gives an error when ran; I assume it does not work because SelectedRange is a range and not a cell.

SelectedRange() searches for the input string and returns a range of each cell that matches the input, which is a modified version of this code: http://www.thespreadsheetguru.com/the-code-vault/2014/4/21/find-all-instances-with-vba

Function SelectedRange(ByVal fnd As String) As Range

Dim FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range

Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.find(what:=fnd, after:=LastCell)

'Test to see if anything was found
  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
  Else
    GoTo NothingFound
  End If

Set rng = FoundCell

'Loop until cycled through all unique finds
  Do Until FoundCell Is Nothing
    'Find next cell with fnd value
      Set FoundCell = myRange.FindNext(after:=FoundCell)

    'Add found cell to rng range variable
      Set rng = Union(rng, FoundCell)

    'Test to see if cycled through to first found cell
      If FoundCell.Address = FirstFound Then Exit Do

  Loop

  Set SelectedRange = rng

'Report Out Message
  MsgBox SelectedRange.Cells.Count & " cell(s) were found containing: " & fnd

Exit Function

'Error Handler
NothingFound:
  MsgBox "No cells containing: " & fnd & " were found in this worksheet"

End Function

Thanks.

score 2 · Accepted Answer · edited May 23 '17 at 12:05

You haven't disclosed exactly what SelectedRange references; hopefully it isn't a union of discontiguous cells.

Use SelectedRange in a With...End With statement and you can access its dimensions for the Resize property.

dim SelectedRange as range
set SelectedRange = range("B2:D4")
with SelectedRange
    set SelectedRange = .resize(.rows.count, .columns.count + 3)
end with
SelectedRange.select    'might want to get away from this method

For a discontiguous arrangement of cells, you need to loop through the Areas property and resize each while adding it to a collective with the Union method.

Dim a As Long, selectedRange As Range, tmpRange As Range
Set selectedRange = Selection
Set tmpRange = selectedRange.Range("A1")
With selectedRange
    For a = 1 To .Areas.Count
        With .Areas(a)
            Set tmpRange = Union(tmpRange, .Cells.Resize(.Rows.Count, .Columns.Count + 3))
        End With
    Next a
End With
Set selectedRange = tmpRange
selectedRange.Select   'might want to get away from this method

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Updated with SelectedRange. Is there an issue with a union of discontiguous cells? — NYB, Jun 02 '15 at 13:09
A union of discontiguous cells may have groups (or *areas*) of cells with different numbers of columns. Each has to be dealt with individually. See above. — , Jun 02 '15 at 17:08

Selecting cells next to a scattered range

1 Answers1