Thread: Limitation on Excel 2003 Non-Contiguous Cells?

Hi Again,

I did some more testing on this, and it seems to be when the range becomes
non-contiguous is when this stops working properly. Is there a problem with
unioning non-contiguous ranges? Why would the column count stop increasing?

Thanks
Rael


"Raeldor" <Raeldor@online.nospam> wrote in message
news:uXG1fms2HHA.1208@TK2MSFTNGP05.phx.gbl...
> Hi All,
>
> Is there a limit in Excel 2003 for ranges with non-contiguous cells? I
> have code that is looping through doing a union of some columns (limited
> rows) and not others, and when it gets past 13 columns, the column count
> remains the same even though the cell count is still increasing as new
> columns are added.
>
> The behaviour is very strange.
>
> Thanks
> Rael
>
>

Here's some vba code showing the issue...


' add first column
Set myRange = Range("b5:b50")
MsgBox Str(myRange.Columns.Count)
Set myRange = Union(myRange, Range("c5:c50"))
MsgBox Str(myRange.Columns.Count)
Set myRange = Union(myRange, Range("e5:e50"))
MsgBox Str(myRange.Columns.Count)


"Raeldor" <Raeldor@online.nospam> wrote in message
news:On$Xz0s2HHA.5772@TK2MSFTNGP02.phx.gbl...
> Hi Again,
>
> I did some more testing on this, and it seems to be when the range becomes
> non-contiguous is when this stops working properly. Is there a problem
> with unioning non-contiguous ranges? Why would the column count stop
> increasing?
>
> Thanks
> Rael
>
>
> "Raeldor" <Raeldor@online.nospam> wrote in message
> news:uXG1fms2HHA.1208@TK2MSFTNGP05.phx.gbl...
>> Hi All,
>>
>> Is there a limit in Excel 2003 for ranges with non-contiguous cells? I
>> have code that is looping through doing a union of some columns (limited
>> rows) and not others, and when it gets past 13 columns, the column count
>> remains the same even though the cell count is still increasing as new
>> columns are added.
>>
>> The behaviour is very strange.
>>
>> Thanks
>> Rael
>>
>>
>
>

Hi,

You need to use Area to count columns in non-contiguous range.

For Each rngArea In myrange.Areas
lngColCount = lngColCount + rngArea.Columns.Count
Next
MsgBox lngColCount

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Raeldor" <Raeldor@online.nospam> wrote in message
news:uovpdNt2HHA.4672@TK2MSFTNGP05.phx.gbl...
> Here's some vba code showing the issue...
>
>
> ' add first column
> Set myRange = Range("b5:b50")
> MsgBox Str(myRange.Columns.Count)
> Set myRange = Union(myRange, Range("c5:c50"))
> MsgBox Str(myRange.Columns.Count)
> Set myRange = Union(myRange, Range("e5:e50"))
> MsgBox Str(myRange.Columns.Count)
>
>
> "Raeldor" <Raeldor@online.nospam> wrote in message
> news:On$Xz0s2HHA.5772@TK2MSFTNGP02.phx.gbl...
>> Hi Again,
>>
>> I did some more testing on this, and it seems to be when the range
>> becomes non-contiguous is when this stops working properly. Is there a
>> problem with unioning non-contiguous ranges? Why would the column count
>> stop increasing?
>>
>> Thanks
>> Rael
>>
>>
>> "Raeldor" <Raeldor@online.nospam> wrote in message
>> news:uXG1fms2HHA.1208@TK2MSFTNGP05.phx.gbl...
>>> Hi All,
>>>
>>> Is there a limit in Excel 2003 for ranges with non-contiguous cells? I
>>> have code that is looping through doing a union of some columns (limited
>>> rows) and not others, and when it gets past 13 columns, the column count
>>> remains the same even though the cell count is still increasing as new
>>> columns are added.
>>>
>>> The behaviour is very strange.
>>>
>>> Thanks
>>> Rael
>>>
>>>
>>
>>
>
>

Hello Raeldor,

From your post, my understanding on this issue is: when you union multiple
non-adjacent ranges in Excel, the resulting range's column number is not
correct. If I'm off base, please feel free to let me know.

I have tested your vba code and reproduced the issue. Actually, the union
of non-adjacent ranges will create multiple 'Areas' in the Range object.
The code 'myRange.Columns.Count' will only return the column number of the
first Area (myRange.Areas(1)). In order to get the correct column number of
non-adjacent ranges, you need to iterate the Areas and add up the column
numbers. For instance:
Dim i As Integer
Dim count As Integer
For i = 1 To myRange.Areas.count
count = count + myRange.Areas(i).Columns.count
Next
MsgBox Str(count)

You may also refer to the following kb which describes a very similar
issue. The kb talks about Range.EntireRow, but I find that it also applies
to Range.Columns after my test.
http://support.microsoft.com/kb/q108518/

Sincerely,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.