Function to get Usedrange gets error 91

Question

Trying to write a Union of Ranges Function

I get `Object variable or with block not set"

I am not getting this right (I think):

 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

The Function

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

First of all, use Set keyword to assign an object to a variable, so UnionRange = should be Set UnionRange =. Specify a sheet object when you are retrieving a range, doing so it's not necessary to pass sheet name to the function since Rng.Parent returns the sheet object.

There is example below:

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