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 |
Removing Cells from an Excel Range
|
Removing Cells from an Excel Range
FindNext Method
See Also Applies To Example Specifics Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn't affect the selection or the active cell. expression.FindNext(After) expression Required. An expression that returns a Range object. After Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If this argument isn't specified, the search starts after the cell in the upper-left corner of the range. Remarks When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address. Example This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With -- Don Guillett SalesAid Software "Anton" wrote in message ... Sorry, but I don't see the light. Perhaps I should add that the IDs are not sorted in either list, so that guarding against wrap-around doesn't help. Anton "Don Guillett" wrote: 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 |
Removing Cells from an Excel Range
Sorry, but I just don't see the solution to my problem. Are you suggesting
that I save the addresses of the cells with matching content and to check in subsequent rounds whether a cell address is among the saved addresses or not? And then to compare contents only if this is a "new" cell address? That could be done of course but I doubt that it would be faster than re-checking all cell contents. Maybe I should re-state the problem: - list 1 contains single occurrences of some unique IDs - list 2 also contains single occurrences of some unique IDs - when looping through list 1 to compare the IDs to list 2, all of list 2 is searched at each round when already found matches could be eliminated Anton "Don Guillett" wrote: FindNext Method See Also Applies To Example Specifics Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn't affect the selection or the active cell. expression.FindNext(After) expression Required. An expression that returns a Range object. After Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If this argument isn't specified, the search starts after the cell in the upper-left corner of the range. Remarks When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address. Example This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With -- Don Guillett SalesAid Software "Anton" wrote in message ... Sorry, but I don't see the light. Perhaps I should add that the IDs are not sorted in either list, so that guarding against wrap-around doesn't help. Anton "Don Guillett" wrote: 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 |
All times are GMT +1. The time now is 10:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com