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 |
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) |