Thread
:
Removing Cells from an Excel Range
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett[_4_]
external usenet poster
Posts: 2,337
Removing Cells from an Excel Range
Have a look in vba help for find or findnext
--
Don Guillett
SalesAid Software
"Anton" wrote in message
...
Hi All:
For an Excel macro I'd like to know how to remove items (in this case,
individual cells) from a range of cells - but without deleting the cells
from
the worksheet. What the macro does is basically to take two lists of
unique
identifiers and to look for the IDs of list 1 among the IDs of list 2.
This
is done by looping through the range of cells that make up list 1, and
searching for each of the IDs of list 1 in the range of cells that make up
list 2. If a match is found the macro does some more work, but the
question
I have relates to the part that compares the lists only. In this part of
the
macro I'd like to take advantage of the efficient built-in Find method.
So basically:
For Each cell in Range1
Set r = Range2.Cells.Find(cell.Value)
If Not r Is Nothing Then
' do some work
End If
Next
The problem I have is that even though the IDs are unique, the macro will
continue to search through all the IDs of list 2 (Range2), even those that
have been matched already. To me the solution would seem to be to remove
matched cells from Range2 so that Find doesn't search them again.
However, I
did not find a way of doing that. The Delete method is not useful because
it
removes the cells from the worksheet, which I don't want to do. What I do
want to do is change the selected range of cells - but how? Especially in
a
way that doesn't waste more time redefining Range2 than there is saved by
searching a shrinking list?
I'd appreciate to get your help and input on this problem, and apologize
if
I failed to find the answer in existing posts. Many thanks already in
advance,
Anton
Reply With Quote
Don Guillett[_4_]
View Public Profile
Find all posts by Don Guillett[_4_]