选择分散范围旁边的单元格

纽约市

我在选择VBA中的单元格范围时遇到困难。我已经有一个分散的单元格区域，但是正在尝试将三个单元格添加到所选区域范围内每个单元格的右侧。

到目前为止，我已经尝试过：SelectedRange("Hi").Resize(1, 4).Select，运行时给出错误；我认为它不起作用，因为SelectedRange是一个范围而不是一个单元格。

SelectedRange（）搜索输入字符串，并返回与输入匹配的每个单元格的范围，此范围是此代码的修改版本：http : //www.thespreadsheetguru.com/the-code-vault/2014/4/21 /使用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

谢谢。

用户4039065

您还没有透露究竟是什么的selectedRange参考; 希望它不是不连续细胞的结合。

在With ... End With语句中使用SelectedRange，您可以为Resize属性访问其尺寸。

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

对于不连续的单元格排列，您需要遍历Areas属性并调整每个元素的大小，然后使用Union方法将其添加到集合中。

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

有关摆脱依赖于选择和激活来实现目标的更多方法，请参见如何避免在Excel VBA宏中使用“选择”。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。