Union with Nothing
Otto Moehrbach
Got a problem.
I have a Union statement:
Set RngAll = Union(RngA, RngB, RngC, RngD)
Works well.
Problem: Code prior to the Union statement may set one or more of the
ranges to Nothing for condition, as in:
If RngA.....................Then _
Set RngA = Nothing
The above Union command no longer works.
Question: Short of a nest of "If" and "Union" statements, how can I modify
the Union statement such that it will accept only the valid ranges?
Thanks for all your help. Otto
Tom Ogilvy
to test all your ranges and only union with those that are not nothing.
an easy way may be to create an array of ranges
Dim rng() as Range, rng1 as Range
Redim rng(1 to 4)
' code to set elements of rng
set rng(1) = Range("A1:A10"
' for example
for i = lbound(rng) to ubound(rng)
if not rng(i) is nothing then
if not rng1 is nothing then
set rng1 = Union(rng1,rng(i))
else
set rng1 = rng(i)
end if
End if
Next
Regards,
Tom Ogilvy
Otto Moehrbach <ot...@worldnet.att.net> wrote in message
news:ON4HkrAsCHA.2556@TK2MSFTNGP09...
Andy Pope
You could try the following which uses a temporary variable to hold a
valid range. This checks and sets the variables against the dummy range.
Hope this is clear. Post back if not.
'---------<Code Start>----------
Sub Test()
'
' Create Union of 4 ranges
'
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim rngD As Range
Dim rngAll As Range
Set rngA = ActiveSheet.Cells(1, 1)
Set rngB = ActiveSheet.Cells(2, 1)
Set rngC = ActiveSheet.Cells(3, 1)
Set rngD = ActiveSheet.Cells(4, 1)
Set rngC = Nothing ' set to nothing for testing
Set rngAll = MakeUnion(rngA, rngB, rngC, rngD)
If rngAll Is Nothing Then
Debug.Print "Union is nothing"
Else
Debug.Print "Union address "; rngAll.Address
End If
End Sub
'--------------------------------
Function MakeUnion(ByVal rngA As Range, ByVal rngB As Range, ByVal rngC
As Range, ByVal rngD As Range) As Range
'
' Make a union of the 4 passed ranges.
' Handle possible value of range is nothing
'
Dim rngDummy As Range
' rngDummy starts as nothing
' test rngDummy for being nothing, use first valid passed range
If rngDummy Is Nothing Then Set rngDummy = rngA
If rngDummy Is Nothing Then Set rngDummy = rngB
If rngDummy Is Nothing Then Set rngDummy = rngC
If rngDummy Is Nothing Then Set rngDummy = rngD
' test passed ranges for being nothing
' update to dummy if true
If rngA Is Nothing Then Set rngA = rngDummy
If rngB Is Nothing Then Set rngB = rngDummy
If rngC Is Nothing Then Set rngC = rngDummy
If rngD Is Nothing Then Set rngD = rngDummy
If rngDummy Is Nothing Then
Set MakeUnion = rngDummy
Else
Set MakeUnion = Union(rngA, rngB, rngC, rngD)
End If
End Function
'---------<Code End>----------
Otto Moehrbach wrote:
--
Cheers
Andy
Patrick Molloy
You could also use a funtion returning True or False with the ON ERROR
RESUME NEXT Method in the function.
Passing the RngAll variable BYREF means the function can set its value as
well as returning true or false.
You'd use the function, test whether it returned true - ie all ranges were
set or false - and handle the event.
Here's a set SUB and the Function to demonstrate.
Sub testunion()
Dim RngAll As Range, RngA As Range, RngB As Range, RngC As Range, RngD As
Range
Dim test As Integer
Set RngA = Range("A1")
Set RngB = Range("A1")
Set RngC = Range("A1")
For test = 0 To 1
' on the first run, don't set the last range - force a "FAIL"
If test = 1 Then Set RngD = Range("A1")
If GetUnion(RngAll, RngA, RngB, RngC, RngD) Then
MsgBox "Test " & test & ":" & "OK"
Else
MsgBox "Test " & test & ":" & "Failed"
End If
Next test
End Sub
Private Function GetUnion(ByRef rng As Range, RngA As Range, RngB As Range,
RngC As Range, RngD As Range) As Boolean
On Error Resume Next
Set rng = Union(RngA, RngB, RngC, RngD)
If Err.Number <> 0 Then
Err.Clear
GetUnion = False ' not req'd as its the default...but easier to see if
coded
Else
GetUnion = True
End If
On Error Goto 0
End Function
--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:ON4HkrAsCHA.2556@TK2MSFTNGP09...
David McRitchie
> You can't union with nothing, so to get a successful outcome, you will need
> to test all your ranges and only union with those that are not nothing.
Never got much into Set Theory, but would the UNION of
an empty set be undefined, or is this just an Excel restriction.
David McRitchie, Microsoft MVP - Excel
Otto Moehrbach
situation to a Tee. You guys make my life easier. Thanks again. Otto
Dave Peterson
IIRC, the union of anything with an empty set is the thing you started with.
The intersection of anything with the empty set would be empty.
--
Dave Peterson
ec3...@msn.com
Dana DeLouis
For the simple Union of two ranges, I use this:
Function MyUnion(r1 As Range, r2 As Range) As Range
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Returns the Union of 2 areas.
'// Returns 'Nothing' only if both are 'Nothing'
'// By: Dana DeLouis
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =
On Error Resume Next
Set MyUnion = Union(r1, r2)
If Not MyUnion Is Nothing Then Exit Function
If Not r1 Is Nothing Then Set MyUnion = r1
If Not r2 Is Nothing Then Set MyUnion = r2
End Function
For 3 or more ranges, I use this:
Function MyUnionList(ParamArray V() As Variant) As Range
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Returns a Range that is the Union of a list of Ranges
'// Returns "Nothing" if all ranges are nothing
'// By: Dana DeLouis
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =
Dim rngOut As Range
Dim vItem As Variant
On Error Resume Next
For Each vItem In V
If Not rngOut Is Nothing Then
Set rngOut = Union(rngOut, vItem)
Else
Set rngOut = vItem
End If
Next
Set MyUnionList = rngOut
End Function
HTH.
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:ON4HkrAsCHA.2556@TK2MSFTNGP09...