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

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

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