Accessing specific items in a range
 

Just found this forum but also here I'm not able to find a answer to am
question.

Data in my sheet is arranged such that sometimes there's 1 or mor
spaces in between pieces of data. I thus create ranges usin
.SpecialCells(xlConstants) which works wonderfully.

My problem now is that the only way to run through the range to fin
something is to use the For Each option. I can't access e.g. the 5t
piece of data in such a range other then using For Each until I get t
the 5th item.

Does anyone know of a way to directly access items in ranges creat
using .SpecialCells(xlConstants)?

Thanks and best regards,

Leo

--
droopy928g
-----------------------------------------------------------------------
droopy928gt's Profile: http://www.excelforum.com/member.php...fo&userid=3023
View this thread: http://www.excelforum.com/showthread.php?threadid=49906

Accessing specific items in a range
 

If you mean fifth group of data (or if each group is a separate cell):
Dim rng as Range
set rng = ActiveCell.EntireColumn.SpecialCells(xlConstants)
? rng.Address
$E$2:$E$4,$E$6,$E$8:$E$10,$E$13:$E$14,$E$16:$E$19, $E$21,$E$23,$E$28:$E$30
? rng.Areas(5).Address
$E$16:$E$19

use Areas.

If the groups have even numbers of cells, then you can calculate.

If the groups have variable numbers of cells, then you would need to loop



--
Regards,
Tom Ogilvy

"droopy928gt"
wrote in message
...

Just found this forum but also here I'm not able to find a answer to amy
question.

Data in my sheet is arranged such that sometimes there's 1 or more
spaces in between pieces of data. I thus create ranges using
SpecialCells(xlConstants) which works wonderfully.

My problem now is that the only way to run through the range to find
something is to use the For Each option. I can't access e.g. the 5th
piece of data in such a range other then using For Each until I get to
the 5th item.

Does anyone know of a way to directly access items in ranges create
using .SpecialCells(xlConstants)?

Thanks and best regards,

Leon


--
droopy928gt
------------------------------------------------------------------------
droopy928gt's Profile:
http://www.excelforum.com/member.php...o&userid=30232
View this thread: http://www.excelforum.com/showthread...hreadid=499064

Accessing specific items in a range
 

Hi Tom,

Thanks for the reply.

When I refer to data I'm referring to a cell with data in it, not an
area.

When a range is made up of consistent non empty cells you can use
..Item(5) to select the 5th item from that range. When I use this in
combination with a range made using .SpecialCells(xlConstants) then
item 1 will be the first item from the range where ever it may be
located, which is correct. Further items though are not correct in
cases where there are empty cells in between pieces of data.

In case of e.g. a range containing data in cells J7,J10,J11. What you
would get is e.g item 1 is J7, Item 2 is J8 and and item 3 is J9.

So I was hoping for there to be a way to directly address data in a
range using a accessing method like .item(x).

From your reply though this doesn't sound like being possible, right?


--
droopy928gt
------------------------------------------------------------------------
droopy928gt's Profile: http://www.excelforum.com/member.php...o&userid=30232
View this thread: http://www.excelforum.com/showthread...hreadid=499064

Accessing specific items in a range
 

I explained what can be done.

--
Regards,
Tom Ogilvy


"droopy928gt"
wrote in message
...

Hi Tom,

Thanks for the reply.

When I refer to data I'm referring to a cell with data in it, not an
area.

When a range is made up of consistent non empty cells you can use
Item(5) to select the 5th item from that range. When I use this in
combination with a range made using .SpecialCells(xlConstants) then
item 1 will be the first item from the range where ever it may be
located, which is correct. Further items though are not correct in
cases where there are empty cells in between pieces of data.

In case of e.g. a range containing data in cells J7,J10,J11. What you
would get is e.g item 1 is J7, Item 2 is J8 and and item 3 is J9.

So I was hoping for there to be a way to directly address data in a
range using a accessing method like .item(x).

From your reply though this doesn't sound like being possible, right?


--
droopy928gt
------------------------------------------------------------------------
droopy928gt's Profile:
http://www.excelforum.com/member.php...o&userid=30232
View this thread: http://www.excelforum.com/showthread...hreadid=499064