Remove cell from Range (object)

Sgdva picture Sgdva · Aug 23, 2016 · Viewed 7.7k times · Source

Background
My code does some loops over ranges, however, each interaction should be performed in the range excluding the cell just performed. I think the easier way to do so is to remove the cell from the stored range.
Problem
I have not been able to find a way to remove cell from the stored object
Code
The question is general but, for the matters it would be something like

Sub Sample()
Dim RangeToAnalyze As Range
Dim CounterRange As Long
Dim ExcludeCell As Range 'sample on what is desired to achieve
    Set RangeToAnalyze = Selection 'this is based on some other criteria but, in order to reproduce it easier that's why selection
    For CounterRange = 1 To 5
    Set ExcludeCell = RangeToAnalyze.Find("text")
    'now here I would like to find the next cell, but it should exclude the first one in order to go to the next one
    Set RangeToAnalyze = RangeToAnalyze.Exclude(ExcludeCell) 'this is what I want to do, so when looping it could jump to the next find (This function is "sample" this is what I am looking to do
    Next CounterRange
End Sub

Answer

cyboashu picture cyboashu · Aug 23, 2016

One approach could be this

Function getExcluded(ByVal rngMain As Range, rngExc As Range) As Range

    Dim rngTemp     As Range
    Dim rng         As Range

    Set rngTemp = rngMain

    Set rngMain = Nothing

    For Each rng In rngTemp
        If rng.Address <> rngExc.Address Then
            If rngMain Is Nothing Then
                Set rngMain = rng
            Else
                Set rngMain = Union(rngMain, rng)
            End If
        End If
    Next

    Set getExcluded = rngMain



End Function

Test the function

Sub test()

    MsgBox getExcluded(Range("A1:M10000"), Range("a10")).Address

End Sub