Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set MyRange for non-adjacent cells and iterate through?
What I have in mind to try and do would be easy if I could iterate through
all the cells of a specified block of cells, picking out certain cells that meet a specific criterion, and adding them to MyRange object. These cells would probably be scattered over the original block of cells and many would not be adjacent to any other cell in MyRange. Then I would iterate through all the cells in MyRange to do things, and drop cells out of MyRange until I have exactly what I want. Is it possible to set up a range of non-adjacent cells so they can be iterated through? If so, what's the easiest methods to use to add or delete a cell from such a range? Thank you. Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set MyRange for non-adjacent cells and iterate through?
One way...
dim myRng as range dim myCell as range dim mySubsetRng as range set myrng = activesheet.range("a1:c9") for each mycell in myrng.cells if mycell.value = lcase(mycell.value) then if mysubsetrng is nothing then set mysubsetrng = mycell else set mysubsetrng = union(mysubsetrng,mycell) end if end if next mycell for each mycell in mysubsetrng.cells msgbox mycell.value & vblf & mycell.address next mycell Ed wrote: What I have in mind to try and do would be easy if I could iterate through all the cells of a specified block of cells, picking out certain cells that meet a specific criterion, and adding them to MyRange object. These cells would probably be scattered over the original block of cells and many would not be adjacent to any other cell in MyRange. Then I would iterate through all the cells in MyRange to do things, and drop cells out of MyRange until I have exactly what I want. Is it possible to set up a range of non-adjacent cells so they can be iterated through? If so, what's the easiest methods to use to add or delete a cell from such a range? Thank you. Ed -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set MyRange for non-adjacent cells and iterate through?
Thank you, Dave! It works great!
Ed "Dave Peterson" wrote in message ... One way... dim myRng as range dim myCell as range dim mySubsetRng as range set myrng = activesheet.range("a1:c9") for each mycell in myrng.cells if mycell.value = lcase(mycell.value) then if mysubsetrng is nothing then set mysubsetrng = mycell else set mysubsetrng = union(mysubsetrng,mycell) end if end if next mycell for each mycell in mysubsetrng.cells msgbox mycell.value & vblf & mycell.address next mycell Ed wrote: What I have in mind to try and do would be easy if I could iterate through all the cells of a specified block of cells, picking out certain cells that meet a specific criterion, and adding them to MyRange object. These cells would probably be scattered over the original block of cells and many would not be adjacent to any other cell in MyRange. Then I would iterate through all the cells in MyRange to do things, and drop cells out of MyRange until I have exactly what I want. Is it possible to set up a range of non-adjacent cells so they can be iterated through? If so, what's the easiest methods to use to add or delete a cell from such a range? Thank you. Ed -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Add cells on two adjacent rows but non-adjacent columns | Excel Worksheet Functions | |||
Why Copy/Paste fails using Offset & Resize of myRange? | Excel Discussion (Misc queries) | |||
Help ASAP myRange | Excel Programming | |||
How To Iterate a range of cells? | Excel Programming |