Excel的VBA：如何扩展的范围内给出一个当前选择(Excel VBA: How to Exten

我想要做的事，如：

E18-(1,1) &":" &E18+(1,1)

我的意图是保持范围的选择E18 （值= B）和扩展选择到D16:F20 。

如果我有一个单元格的范围E18 ，我想扩大范围到D16:F20 ，我该怎么办呢？

Answer 1:

Range(Cells(WorksheetFunction.Max(1, Selection.Row - 1), _
      WorksheetFunction.Max(1, Selection.Column - 1)), _
      Cells(WorksheetFunction.Min(Selection.Worksheet.Rows.Count, _
      Selection.Row + 1), _
      WorksheetFunction.Min(Selection.Worksheet.Columns.Count, _
      Selection.Column + 1))).Select

UPD：感谢亚洲时报Siddharth溃败了在格式化我的味精

Answer 2:

你的意思是这样吗？

句法

ExpandRange [ 范围 ]，[COL的左数 ]，[ 在最前行数 ]，[COL右数 ]，[ 行数下降 ]

Sub Sample()
    Debug.Print ExpandRange(Range("B5"), 1, 1, 1, 1)            '<~~ $A$4:$C$6
    Debug.Print ExpandRange(Range("A1"), 1, 1, 1, 1)            '<~~ Error
    Debug.Print ExpandRange(Range("XFD4"), 1, 1, 1, 1)          '<~~ Error
    Debug.Print ExpandRange(Range("XFD1048576"), 1, 1, 1, 1)    '<~~ Error
    Debug.Print ExpandRange(Range("E5"), 1, 1, 1, 1)            '<~~ $D$4:$F$6
End Sub

Function ExpandRange(rng As Range, lft As Long, tp As Long, _
rt As Long, dwn As Long) As String
    If rng.Column - lft < 1 Or _
       rng.Row - tp < 1 Or _
       rng.Column + rt > ActiveSheet.Columns.Count Or _
       rng.Row + dwn > ActiveSheet.Rows.Count Then
        ExpandRange = "Error"
        Exit Function
    End If

    ExpandRange = Range(rng.Offset(-1 * tp, -1 * lft).Address & ":" & _
                        rng.Offset(dwn, rt).Address).Address
End Function

Answer 3:

下面是我用它来调整现有选择简单的代码。

Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count + 50).Select

这将增加5到行数和50列数。 适应满足您的需求。

Answer 4:

您可以使用Application.WorksheetFunction.Offset()比VBA的对这个问题要求补偿和做事都更丰富。
我觉得它做什么，亚洲时报Siddharth溃败ExpandRange做，而不需要一个UDF的。

Answer 5:

而不是返回绝对地址的，我修改上面的语法返回一个范围。 幸得亚洲时报Siddharth溃败=）

Function ExpandRG(rng As Variant, lft As Long, tp As Long, rt As Long, dwn As Long) _
 As Range
 Set ws = rng.Parent
If rng.Column - lft < 1 Or _
   rng.Row - tp < 1 Or _
   rng.Column + rt > ActiveSheet.Columns.Count Or _
   rng.Row + dwn > ActiveSheet.Rows.Count Then
        MsgBox "Out of range"
        Exit Function
End If

 Set rng = ws.Range(rng.Offset(-1 * tp, -1 * lft).Address & ":" & _
                    rng.Offset(dwn, rt).Address)                        
End Function

Sub aa()
Dim ori_add, O_add, New_add As Range
Set ori_add = Range("B2")
Set O_add = ori_add

Call ExpandRG(ori_add, 1, 1, 1, 1)
Set New_add = ori_add

MsgBox "Original address " & O_add.Address & ", new address is" & New_add.Address
End Sub

文章来源: Excel VBA: How to Extend a Range Given a Current Selection