Repost: Removing Cells from a Range

[Guest]

As I must have been unclear in my previous post I’ll try again: How can one
re-size a range object? What I want to do is successively shrink the range
of cells that are used by a macro that I work on. The macro successively
searches for IDs in a specified range of cells, and if an ID is found then I
want to remove that cell from the range so that it is no longer part of
further rounds of searching. But as I don’t want to modify the worksheet
Range.Delete doesn’t work for me.

I’d appreciate any help in solving this problem.

Anton

 

[Tom Ogilvy]

You description is certainly understandable. However, it is so general this
is little that anyone could provide.

Perhaps if you created a collection and then each time you identified a
cell, you added the address of the cell to the collection. Then when you
searched again, you could continue searching until you found something not
in the collection.

Really hard to say without some understanding of your data and what you are
trying to do.

 

[Guest]

How aabout re-creating the range, but excluding the part you want to remove?

Function outpt(inpt As Range, remove As Range) As Range
Dim r As Range
For Each r In inpt
If Intersect(r, remove) Is Nothing Then
If outpt Is Nothing Then
Set outpt = r
Else
Set outpt = Union(r, outpt)
End If
End If
Next
End Function

just concept code - untested

 

[Guest]

Hi,

Thanks, this does indeed work but it's quite a bit of work that the macro
has to do for what I thought should be a simple task. Please see my reply to
Tom Ogilvy's post for details on what my macro does and why I would like to
shrink a range.

Anton

 

[Guest]

Hi,

Well, I thought that in my first post I had cluttered things up too much
with details...

You are right though, my repost is very general. Here is what my macro is
supposed to do: I am working with gene expression data, with each gene
identified by a unique ID. The expression of each gene differs in different
experiments, so that by certain filtering steps I get different lists of
"interesting" genes for different experiments. What the macro is supposed to
do is compare lists of unique gene IDs. These lists can contain tens of
thousands of IDs so that processing time is of some concern.

So I have a list 1 of IDs in the cells of Range1, and a list 2 of IDs in the
cells of Range2. The macro looks for the IDs of list 1 among the IDs of list
2, and if there's a match it does some additional work. Basically it's like
this:

For Each cell in Range1
Set r = Range2.Cells.Find(cell.Value)
If Not r Is Nothing Then
‘ do some work
End If
Next

Now, as the IDs are unique the macro could save time by successively
removing cells with already matched IDs from Range1. To take an extreme
example: if Range1 has only one ID, and Range2 has 10,000 IDs and the first
one is the same as that of Range1, the macro would still search all remaining
9,999 IDs of Range2 to see if it matches the ID in Range1 (such extreme cases
can be avoided by using the shorter of the two lists as query, but you get my
point).

So here is pseudocode of what I want to do:

For Each cell in Range1 While Not Range2 Is Nothing
Set r = Range2.Cells.Find(cell.Value)
If Not r Is Nothing Then
Set Range2 = Range2 minus r
‘ do some work
End If
Next

And this "Range2 minus r" is what I don't know how to do. "Gary's student"
has provided a solution in his reply, but it seems very complex for what I
thought should be simple.

I hope this was helpful rather than confusing...

Many thanks,

Anton