Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Cells from an Excel Range
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SAP to Excel. Removing blank cells HELP!!!! | Excel Discussion (Misc queries) | |||
remove the cells contents (data) without removing formulas Excel 2 | Excel Worksheet Functions | |||
Removing apostraphes from a group of cells on an Excel spreadsheet | Excel Worksheet Functions | |||
Removing range names. | Excel Discussion (Misc queries) | |||
Excel should merge cells without removing content of any cell | Excel Discussion (Misc queries) |