SpecialCells & AddressLocal
 

Hi all,

Currently I'm trying to use the function specialcells to iterate trough
several non contiguos range in a spreadsheet. I am using this function,
cause there are activated the autofilter on the Sheet.

Now the problem pop up. This is a snipped from the code

Dim rng as Range
Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlVisibl e).Rows
'loop trought the string provided
loop_str = rng.AddressLocal

loop_str string contains the reference to the various differente ranges
separated by a comma.

example content:

$A$7:$O$7,$A$57:$O$57,$A$59:$O$59,$A$61:$O$61,$A$6 4:$O$64,$A$67:$O$68,
$A$70:$O$71,$A$74:$O$74,$A$84:$O$87,$A$99:$O$99,$A $103:$O$104,$A$107:$O$110,
$A$120:$O$121,$A$123:$O$123,$A$126:$O$126,$A$128:$ O$129,$A$131:$O$131,
$A$134:$O$134,$A$140:$O$141
* Note: the text is just in one line, I separated it here for clarity
sake ! *

Till here all is fine, the problem is that I got at max 19 different
ranges, even if there are more.

I believe that this problem is due the fact that a string can be long
at max 255 chars, so I am looking for a workaroung.

Can anyone tell me what shall I do ? Thank you in advance

SpecialCells & AddressLocal
 

Hi
i've worked around this kind of thing by getting the addresses one row
at a time and adding them to a collection. Then you can build other
ranges from that by concatenating these address strings. Hard to be
specific without knowing exactly what you want to do with the
addresses.

regards
Paul

SpecialCells & AddressLocal
 

Hi Paul,

Thank you for the reply. Oki, I fixed the code now going trough the
collection.

It was a quick fix and I was walking the long way of the beginning..
which leaded me to the dark side :) now... everything is neat and
perfectly working ! :)

Thank you again !

SpecialCells & AddressLocal
 

I'm not sure what you're doing, but couldn't you have just looped through the
range object itself--and bypass all the addresses?

dim myArea as range
for each myarea in rng.areas

dim myRow as range
for each myrow in rng.rows

dim mycell as range
for each mycell in rng.cells



wrote:

Hi all,

Currently I'm trying to use the function specialcells to iterate trough
several non contiguos range in a spreadsheet. I am using this function,
cause there are activated the autofilter on the Sheet.

Now the problem pop up. This is a snipped from the code

Dim rng as Range
Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlVisibl e).Rows
'loop trought the string provided
loop_str = rng.AddressLocal

loop_str string contains the reference to the various differente ranges
separated by a comma.

example content:

$A$7:$O$7,$A$57:$O$57,$A$59:$O$59,$A$61:$O$61,$A$6 4:$O$64,$A$67:$O$68,
$A$70:$O$71,$A$74:$O$74,$A$84:$O$87,$A$99:$O$99,$A $103:$O$104,$A$107:$O$110,
$A$120:$O$121,$A$123:$O$123,$A$126:$O$126,$A$128:$ O$129,$A$131:$O$131,
$A$134:$O$134,$A$140:$O$141
* Note: the text is just in one line, I separated it here for clarity
sake ! *

Till here all is fine, the problem is that I got at max 19 different
ranges, even if there are more.

I believe that this problem is due the fact that a string can be long
at max 255 chars, so I am looking for a workaroung.

Can anyone tell me what shall I do ? Thank you in advance

--

Dave Peterson

SpecialCells & AddressLocal
 

Yes Dave,

Now I'm using the "navigate the collection" way. I don't know why the
other one is wrong... :(

THx !