Remove cell from Range (object)

Question

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

As far as I am aware, there is no native way to do this functionality. You have a couple of options though. You could create a UDF that will remove a cell from a range, you could alter how you define your range to begin with, or you could loop over the range as normal and have a check for "text" and if the cell contains the text then don't run code on that cell and the loop skips to the next one. I recommend the last option personally, it will require the least amount of tweaking to your current code and will still run quickly. — tigeravatar, Aug 23 '16 at 14:16
I guess an UDF would be the approach, you just gave me an idea on how to do so. Too bad there isn't a way to do this in a native way. — Sgdva, Aug 23 '16 at 14:20
You are not going to like the answer, but you will have to do a bunch of `Intersect` or `Union`. Are you trying to find the 5th `"text"` ? — Slai, Aug 23 '16 at 14:22

score 8 · Accepted Answer · answered Aug 23 '16 at 14:47

8

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

answered Aug 23 '16 at 14:47

cyboashu

10,196
2
27
46

1

This was the thing I was working on with the first comment, yeah this is what I was looking for! – Sgdva Aug 23 '16 at 15:30
For further references, I just spotted, this won't work if the argument parsed to rngMain is a whole column/row, it should be limited within the function calling this on – Sgdva Aug 23 '16 at 16:58
2

@Sgdva Change `If rng.Address <> rngExc.Address Then` to `If Intersect(rng, rngExc) Is Nothing Then` – Carrosive Apr 27 '18 at 12:03

score 3 · Answer 2 · answered Aug 23 '16 at 14:21

3

You would be better to use a For... Each loop I suspect. This should be a starting place:

Sub Sample()

    Dim RangeToAnalyze As Range
    Dim rngCell as Range

    Set RangeToAnalyze = Range("Selection")

    For each rngCell in RangeToAnalyze
        'Your other code/actions here
    Next rngCell

    'more code here

End Sub

This should then perform your actions on each cell and move on to the next one and automatically stop at the last cell.

You can also nest this inside another For... Each loop to cycle different ranges as well and so on.

answered Aug 23 '16 at 14:21

Matt_Roberts

444
1
7
15

I guess this is a workaround for the matters, however, I need to control the cycle in the `For each`. I could add a counter but, if so, I'd get back to a `For/To` logic – Sgdva Aug 23 '16 at 14:24
@Sgdva in what way do you need to control the cycle? You can add if statements after the for... each to move to another part of the code or exit the loop. – Matt_Roberts Aug 23 '16 at 14:27
The logic is a loop through a "Find", not for each, code will take so long analyze each cell instead of performing the find, based on the occurrence for it, another things are triggered (if text found is the 5th then..) for example – Sgdva Aug 23 '16 at 14:29
@Sgdva so are you trying to find the text string "5th" inside the cell value? – Matt_Roberts Aug 23 '16 at 14:31
Nope, I'm looping over and for each interaction I do something different If find 1 then x=2 if find 2 then y=x/3 for example – Sgdva Aug 23 '16 at 15:10
@Sgdva I might be missing something then as I would still think you can create your conditions within the loop using multiple if or case statements – Matt_Roberts Aug 24 '16 at 08:11
I needed the function to get the "re set" the range within excluded cells in order to work with that one instead, so then I would be available to call it in a main logic, accepted answer could give you a hint on what was aiming for. I need the range to work with in another code, not something isolated that's why this logic wouldn't work for me because rangeToAnalyze has to change excluding some cells and do different logic based on that (after it was re set, not during the loop), that's why is easier to re define the object and work accordingly – Sgdva Aug 24 '16 at 15:29

Slai · Answer 3 · 2016-08-23T15:34:34.287

To find the 5th "text" you can use .FindNext

    Set ExcludeCell = RangeToAnalyze.Find("text")

    Dim CounterRange As Long
    For CounterRange = 1 To 5
        If Not ExcludeCell Is Nothing Then 
            Select Case CounterRange 
                Case 1: 
                Case 2: 
                Case 3: 
                Case 4: 
                Case 5: 
            End Select
        End If
        Set ExcludeCell = RangeToAnalyze.FindNext
    Next CounterRange
    'If Not ExcludeCell Is Nothing And CounterRange = 5 Then MsgBox ExcludeCell.Address

Another alternative can be to replace the found "text" with something else temporarily

    For CounterRange = 1 To 5
        Set ExcludeCell = RangeToAnalyze.Find("text")
        If Not ExcludeCell Is Nothing Then 
            Select Case CounterRange 
                Case 1: 
                Case 2: 
                Case 3: 
                Case 4: 
                Case 5: 
            End Select
        End If
    Next CounterRange

    ' use RangeToAnalyze

    RangeToAnalyze.Replace "not text", "text"

Alternative to the alternative is to store the 5 "text" ranges into one Range with Union, clear the values of that range, and then set them back to "text" when done

Thanks! But, this is a loop for all the elements within, I'd have to make this logic for 1,2,3,n... That's why I need something general to call on depending on the case needed. — Sgdva, Aug 23 '16 at 15:22

Remove cell from Range (object)

3 Answers3

Linked

Related