Accessing specific items in a range

[droopy928gt]

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

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[droopy928gt]

Hi Tom,

Thanks for the reply.

When I refer to data I'm referring to a cell with data in it, not an area. Cells containing data are randomly placed and as such there's no constant usable to calculate. Latter is also the reason for using .SpecialCells(xlConstants).

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 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 such a .SpecialCells(xlConstants) range using a accessing method like .item(x).

From your reply though this doesn't sound like being possible, right? :-( I'm so much hoping your wrong though. ;-)

Thanks and best regards,

Leon

[Tom Ogilvy]

I explained what can be done.

--
Regards,
Tom Ogilvy


"droopy928gt" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>