Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=COUNTIF(range, select items highlighted with specific color) | Excel Worksheet Functions | |||
Counting items with a specific quanitifier in a date range | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
How do I copy specific items to another worksheet? | Excel Worksheet Functions | |||
Accessing/Clearing range in hidden workbook | Excel Programming |