Removing Cells from an Excel Range
Hi All:
For an Excel macro Id 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 Id 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 doesnt 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 dont want to do. What I do
want to do is change the selected range of cells €“ but how? Especially in a
way that doesnt waste more time redefining Range2 than there is saved by
searching a shrinking list?
Id 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
|