What is the most efficent way to get a Range "set difference" in Excel Automation?

Question

Given a pair of ranges, where one is known to contain elements of another:

src = ActiveSheet.UsedRange
sel = src.SpecialCells(xlCellTypeVisible)

what is the most efficient way to get a new range that represents the set difference between these two ranges, i.e., the cells in src that are not in sel?

This can be done using a loop over the cells in src, but this requires m * n Automation calls for an m by n range, which does not have very good performance, and I'm hoping for something that involves fewer remote API calls.

Answer 1

I don't think there is any better way than looping through each cell in src. The Excel.Application object has the Intersect function, which tells you what cells are contained in two or more ranges, but not the opposite. You can also perform a union which just gives you a range with all the cells in two or more ranges...

But I think you're going to have to go through each cell in src and see if it's in sel...

Function GetInvisibleCells() As Range
    Dim src As Range, sel As Range

    Set src = ActiveSheet.UsedRange
    Set sel = src.SpecialCells(xlCellTypeVisible)

    Dim Isection As Range
    Set Isection = Application.Intersect(src, sel)

    Dim Result As Range

    Dim cl As Range
    For Each cl In src ' Go find all the cells in range2 that aren't in Isection
        If Application.Intersect(cl, Isection) Is Nothing Then
            ' Cl is not in sel
            If Not Result Is Nothing Then
                Set Result = Range(Result, cl)
            Else
                Set Result = cl
            End If
            ' Debug.Print Cl.Address & " is in " & src.Address & " and not in " & sel.Address
        End If
    Next cl
    Application.Intersect
    Set GetInvisibleCells = Result

End Function

There are a few approaches detailed here

Answer 2

Have you tried using Excel's built-in formulas to filter the data? For example, the following formula will print "Yes" if the value in cell A2 is found in column D. In your case, you could simply grab all the cells which do not have the corresponding "Yes".

=IF(MATCH(A2,D:D, 0)>0,"Yes","No")