Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Removing Cells from a Range
As I must have been unclear in my previous post Ill 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 dont want to modify the worksheet Range.Delete doesnt work for me. Id appreciate any help in solving this problem. Anton |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Removing Cells from a Range
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. -- Regards, Tom Ogilvy "Anton" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Removing Cells from a Range
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 -- Gary''s Student "Anton" wrote: As I must have been unclear in my previous post Ill 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 dont want to modify the worksheet Range.Delete doesnt work for me. Id appreciate any help in solving this problem. Anton |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Removing Cells from a Range
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 "Gary''s Student" wrote: 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 -- Gary''s Student "Anton" wrote: As I must have been unclear in my previous post Ill 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 dont want to modify the worksheet Range.Delete doesnt work for me. Id appreciate any help in solving this problem. Anton |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Removing Cells from a Range
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Anton" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Range REPOST | Excel Worksheet Functions | |||
repost: seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
Removing Cells from an Excel Range | Excel Programming | |||
repost: plz help- dynamic range with gaps? | Excel Discussion (Misc queries) | |||
Repost: Any way to do this with a range? | Excel Programming |