excel - 获取 Usedrange 的函数出现错误 91

Question

尝试编写范围函数的联合

我得到“对象变量或未设置 block ”

我没有做对(我认为):

 With Rng
     UnionRange = Intersect(ws.UsedRange, Rng.EntireColumn)
 End With



Sub iUnionRange()
Dim R As Range

'Check to see if the Function is working
Set R = UnionRange("Elements", Range("A1:D1, G1:G1, I1:K1"))
R.Select

End Sub

功能

Function UnionRange(shtName As String, Rng As Range) As Range

 Set ws = ThisWorkbook.Sheets(shtName)
 If Rng Is Nothing Then Exit Function

 With ws.Rng
     UnionRange = Intersect(ws.UsedRange, .EntireColumn)
 End With

End Function

Answer 1

首先，使用Set将对象分配给变量的关键字，因此 UnionRange =应该是 Set UnionRange = .在检索范围时指定工作表对象，这样做不需要将工作表名称传递给函数，因为 Rng.Parent返回工作表对象。

下面有一个例子:

Sub test()
    Dim Q As Range
    Dim R As Range
    Set Q = Sheets("Elements").Range("A1:D1, G1:G1, I1:K1")
    Q.Select
    Set R = UnionRange(Q)
    R.Select
End Sub

Function UnionRange(Rng As Range) As Range
    If Rng Is Nothing Then Exit Function
    Set UnionRange = Intersect(Rng.Parent.UsedRange, Rng.EntireColumn)
End Function