Excel

Get inverse of range returned by Intersect

Ease of Use

Intermediate

Version tested with

2010, 2013 

Submitted by:

mdmackillop

Description:

After defining a range which forms a subset of a larger range, the function will return the inverse of the defined range. 

Discussion:

Search for a single value and return the range which excludes that value. 

Code:

instructions for use 

			
Function InverseRange(rFull As Range, rPart As Range) As Range 
    Dim tmp As Range 
    Dim cel As Range 
    For Each cel In rFull.Cells 
        If Intersect(cel, rPart) Is Nothing Then 
            If tmp Is Nothing Then 
                Set tmp = cel 
            Else 
                Set tmp = Union(tmp, cel) 
            End If 
        End If 
    Next 
    Set InverseRange = tmp 
End Function

How to use:

  1. Pass the larger and subset ranges to the function to return the inverse
 

Test the code:

  1. See attachment
 

Sample File:

InverseRange.zip 20.37KB 

Approved by GTO


This entry has been viewed 31 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express